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


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 comments:

Madhivanan said...

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

Madhivanan said...

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

Pramod Kasi said...

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.

Madhivanan said...

Pramod,

Suprotim already had a post for version 2000

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

Anonymous said...

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

Suprotim Agarwal said...

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/

Ashish said...

Awesome
thanks for sharing ur knowldge

Aashita Chauhan said...

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