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, reader “Pramod Kasi” asked a question – How to Combine Multiple Rows Into One Column with CSV (Comma Separated) output . This is what he meant:


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:

[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.


Did you like this post?
kick it on
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



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


    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

    [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


    Suprotim already had a post for version 2000

  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)
    DECLARE @result varchar(max)
    SELECT @result = coalesce(@result + '','', '''') + (h.shortname + ''|'' + h.displayname)
    FROM table

    WHERE criteria

    RETURN @Result

  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

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

    thanks for sharing ur knowldge

  8. Aashita Chauhan said...
    June 18, 2015 at 12:08 AM

    But if we do same in multiple tables how???


Copyright © 2009-2016 All Rights Reserved for by Suprotim Agarwal | Terms and Conditions