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:
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:
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
Suprotim,
ReplyDeleteHere 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
The code is
ReplyDeleteSELECT 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
Awesome thank you so much for responding this is exactly what I was looking for.
ReplyDeleteI 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.
Pramod,
ReplyDeleteSuprotim already had a post for version 2000
http://www.sqlservercurry.com/2008/06/combine-multiple-rows-into-one-row.html
This works much better using coalesce and no need for substring and xml path. Much faster.
ReplyDeleteCREATE 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
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.
ReplyDeleteA 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/
Awesome
ReplyDeletethanks for sharing ur knowldge
Awsm..
ReplyDeleteBut if we do same in multiple tables how???