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, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook

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

Unknown said...

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