March 04, 2011

SQL Server: Combine Multiple Rows Into One Column with CSV output




In response to one of my posts on Combining Multiple Rows Into One Row, SQLServerCurry.com reader “Pramod Kasi” asked a question – How to Combine Multiple Rows Into One Column with CSV (Comma Separated) output . This is what he meant:

rowstocolumn

I found the question interesting and frequently asked, so I decided to write a post in response to Pramod’s question. There are three ways in my opinion using which this requirement can be achieved – Using FOR XML, a PIVOT operator or a CLR aggregation function. I will use the FOR XML method (originally shown by Tony Rogerson) in a sub-query to the SELECT, as shown below:

sql rows to column csv

AWESOME isn’t it, making a complex query look so simple. I instant fell in love with this query when I learnt about it a couple of years ago and have used in a few reports as well. If you know a better approach, please share it in the comments section. Here’s the same query for you to try out:

SELECT DISTINCT
[Col1],
[Col2] = SUBSTRING(( SELECT ', ' + [Col2] as [text()]
FROM #Temp t2
WHERE t2.Col1 = t1.Col1
FOR XML path(''), elements
), 2, 100
)
FROM #Temp t1

Note: Adjust the SUBSTRING length as per your requirement. This query will work on SQL Server 2005 and above. If you are using SQL Server 2000, you will have to use a cursor and I honestly do not know how to code that.

OUTPUT


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

 
  Feedback:

comments

7 Responses to "SQL Server: Combine Multiple Rows Into One Column with CSV output"
  1. Madhivanan said...
    March 4, 2011 at 3:19 AM

    Suprotim,

    Here is the generalised method that removes the first character using STUFF function. Using SUBSTRING, you have to specify the number which may not work if the string length exceeds that limit

  2. Madhivanan said...
    March 4, 2011 at 3:19 AM

    The code is

    SELECT DISTINCT
    [Col1],
    [Col2] = STUFF(( SELECT ', ' + [Col2] as [text()]
    FROM #Temp t2
    WHERE t2.Col1 = t1.Col1
    FOR XML path(''), elements
    ), 1, 1,''
    )

    FROM #Temp t1

  3. Pramod Kasi said...
    March 4, 2011 at 11:11 AM

    Awesome thank you so much for responding this is exactly what I was looking for.
    I think I am thinking too much in respect to Performance perspective,
    is it possible to have same results without using DISTINCT and without FOR XML so that I can use it in SQL 2000?
    if we have 1000 rows, then every A A1,A2 gets concatinated 1000 times and then we are doing Distinct on it. So just wondering is it possible? I am learning and trying to understand here please bare with my questions.

  4. Madhivanan said...
    March 6, 2011 at 11:03 PM

    Pramod,

    Suprotim already had a post for version 2000

    http://www.sqlservercurry.com/2008/06/combine-multiple-rows-into-one-row.html

  5. Anonymous said...
    April 10, 2011 at 9:33 AM

    This works much better using coalesce and no need for substring and xml path. Much faster.

    CREATE FUNCTION [dbo].[fnconcatmultrows](@groupcode varchar(10))
    RETURNS varchar(Max)
    AS
    BEGIN
    DECLARE @result varchar(max)
    SELECT @result = coalesce(@result + '','', '''') + (h.shortname + ''|'' + h.displayname)
    FROM table

    WHERE criteria

    RETURN @Result
    END

  6. Suprotim Agarwal said...
    April 10, 2011 at 6:56 PM

    Anonymous: As I mentioned in my post, there are several ways to do this. Check the comment by Madhivanan where has has explained how to do the same using STUFF instead of SUBSTRING.

    A couple of days ago, I stumbled across an excellent post by Anith on the same topic. You should read that

    http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

  7. Ashish said...
    July 21, 2011 at 12:30 AM

    Awesome
    thanks for sharing ur knowldge

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions