SQL Server Performance

Concatenate multiple records into single record

Discussion in 'SQL Server 2005 General Developer Questions' started by Riya86, Aug 18, 2008.

  1. Riya86 New Member

    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
  2. Adriaan New Member

    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.
  3. Riya86 New Member

    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.
  4. Adriaan New Member

    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.
  5. Riya86 New Member

    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
  6. Adriaan New Member

    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.
  7. Madhivanan Moderator

    [quote user="Riya86"]Ya i want it in sinle column on single row. Like i have, num id 123 1 456 1 i want it as 123,456 i 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=10 Select @numberList as numberList But through this i can store the data in a variable,but i want the data in a new table[/quote]
    Why do you want to do this?
    Where do you want to show data?
  8. Riya86 New Member

    i want to use this code in ASP.
  9. atulmar New Member

    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
  10. Madhivanan Moderator

    [quote user="Riya86"]i want to use this code in ASP.[/quote]
    Send actual data to ASP
    Using recordset object, loop thru the data and concatenate them over a variable and display it
  11. atulmar New Member

    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.

Share This Page