Concatenate multiple records into single record

Hi,I want to concatenate multiple records of a column in a single field.likeI have datanum id123 1 456 1765 1i want to make a new table from this asnumber id123,456,765 1can anyone help me in this?I m using ASP & SQL server

Why? If it is only for presentation of the data, then leave the data as is, and use a user-defined function to return the comma-separated list in a column of your query.
Do a search on these forums - there are plenty of threads discussing your requirement.

the data is in vertical order and i want it in vertical order in a new table.then i have to work on it further.

If you mention "concatenation", it is not only about changing from vertical to horizontal order, but also about de-normalizing data.
Will you be putting the single values from the separate rows each into a separate column on a single row (still normalized) or into a single column on a single row (de-normalized)?
If de-normalized, then please note that this violates the ground rules for proper database design, and whenever you need to retrieve a single value from the comma-separated list, you will have to program with string functions, instead of using simple SQL syntax.

Ya i want it in sinle column on single row.Like i have,num id123 1 456 1i want it as123,456i have this code:-DECLARE @numberList varchar(700)Select @numberList = IsNull(mobile_no + ',' + @numberList, NULLIF( mobile_no, @numberList)) from sub_mobile_master where category_id=1 and d_o_start='08/12/2008' and enabled=10Select @numberList as numberListBut through this i can store the data in a variable,but i want the data in a new table

So you loop through the master table to get the unique ID values, which should be used as the FK on the child table, then you run your concatenation statement to get the string into the variable, and finally you insert both the ID and the string into your new table.
If you need to do this often, you might create a User Defined Function - which by the way would be the preferred solution for presentation purposes, over the de-normalized table.

i want to use this code in ASP.

If you are using SQL 2K5 and good in CLR, and CLR procedure deployment is possbile. Then go for that. String concatenation is faster in .Net. But in your case I think as you are using ASP, so I dont think it is possible.
Or
Use one scalar UDF, that will concatenate your string values. In that way you can use this UDF in your query too.
To concatenate, as mentioned earlier in post also. Dont use loop. Use the query like
Select @finalString = @finalString + <Column_Name> + ','
From <Table_Name> -- Remove one charchter (last comma) from last after concatenation

Yes, but It will be slower, when we are looping through records. In SQL this can take benefit of set based operations.
SQL version of this will be faster defintely.