One of the most common questions I receive in email is how to group multiple columns data in comma separate values in a single row grouping by another column.
I have previously blogged about it in following two blog posts. However, both aren’t addressing the following exact problem.
- Comma Separated Values (CSV) from Table Column
- Comma Separated Values (CSV) from Table Column – Part 2
The question comes in many different formats, but in following image I am demonstrating the same question in simple words. This is the most popular question on my Facebook page as well. (Example)
Here is the sample script to build the sample dataset.
CREATE TABLE TestTable (ID INT, Col VARCHAR(4)) GO INSERT INTO TestTable (ID, Col) SELECT 1, 'A' UNION ALL SELECT 1, 'B' UNION ALL SELECT 1, 'C' UNION ALL SELECT 2, 'A' UNION ALL SELECT 2, 'B' UNION ALL SELECT 2, 'C' UNION ALL SELECT 2, 'D' UNION ALL SELECT 2, 'E' GO SELECT * FROM TestTable GO
Here is the solution which will build an answer to the above question.
-- Get CSV values
SELECT t.ID, STUFF(
(SELECT ',' + s.Col
FROM TestTable s
WHERE s.ID = t.ID
FOR XML PATH('')),1,1,'') AS CSV
FROM TestTable AS t
GROUP BY t.ID
GO
I hope this is an easy solution. I am going to point to this blog post in the future for all the similar questions.
Final Clean Up Act
-- Clean up DROP TABLE TestTable GO
Here is the question back to you –
Is there any better way to write above script? Please leave a comment and I will write a separate blog post with due credit.
Reference: Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)
56 Comments. Leave new
I have been using SQL CLR to do this kind of string aggregation, but not sure if that is more efficient than this one or not.
This solution is extreme inefficient if you want to get thousands of records. The best solution for the short concatenations (less then 4000 characters) is to create clr aggregation function with StringBuilder class inside clr code. For the longer concatenations you can build clr function that has query as an argument and results in concatenated value of nvarchar(max) type. This time again you can use StringBuilder class inside clr code. The problem is with resource allocation. Every time you perform concatenation you need to allocate new bigger space. In the StringBuilder solution you take the concatenation elements and create single buffer for all elements. There is no waste of time for memory allocations for single concatenations.
1. We’re not allowed CLR into prod, so that solution fails.
2. How about if you have nvarchar() for data types instead of single values? :-). Hint: SPLIT function…
3. Uh-oh, I actually created a different puzzle altogether…
Just added info in post
This could be alternative solution.
;WITH CTE (SRNO,ID,COL)
AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID,COL) AS SRNO,ID,CAST(COL AS VARCHAR(MAX)) FROM TESTTABLE
)
,CTE1(SRNO,ID,COL)
AS
(
SELECT * FROM CTE WHERE SRNO=1
UNION ALL
SELECT CTE.SRNO AS SRNO, CTE.ID,CAST (CTE1.COL + ‘,’ + CTE.COL AS VARCHAR(MAX)) FROM CTE INNER JOIN CTE1
ON CTE.ID=CTE1.ID AND CTE.SRNO=CTE1.SRNO+1
)
SELECT ID, MAX(COL) AS CSV FROM CTE1 GROUP BY CTE1.ID
Here is an interesting read which pretty much talks everything about the various ways to above problem. Author Anith Sen have done quite extensive work to explain various ways:
Here is the another solution using Recursive CTEs ..
But FOR XML PATH(”) query giving good performance compare to this..
;WITH Rnumcte1 AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY id ORDER BY id,Col) AS Rnum,id,cast(Col as varchar(max)) Col
FROM TestTable
)
,Rnumcte2 AS
(
SELECT Rnum,id,Col
FROM Rnumcte1
WHERE Rnum = 1
UNION ALL
SELECT r1.Rnum,r1.id,r2.Col + ‘,’ + r1.Col
FROM Rnumcte1 r1
INNER JOIN Rnumcte2 r2 ON r1.id = r2.id AND r1.Rnum = r2.Rnum + 1
) –select * from Rnumcte2
SELECT r2.id,r2.Col
FROM (
SELECT id,MAX(Rnum)maxRnum
FROM Rnumcte1
GROUP BY id
) r1
INNER JOIN Rnumcte2 r2 ON r1.id = r2.id AND r1.maxRnum = r2.Rnum
ORDER BY r2.id
Select ID,SUBSTRING(
(Select COALESCE(‘,’+Col,”)
From TestTable
Where ID = Master.ID
For Xml Path(”)),2,500000) As CSV
From TestTable as Master
Group By ID
Create function that will return comma separated value by Id like
Create function [dbo].[GetCSV](@Id int)
Returns nvarchar(Max)
as
Begin
Declare @ListStr nvarchar(Max)
set @ListStr = ”
select @ListStr = @ListStr + Col + ‘,’ from TestTable where Id = @Id
Return substring(@ListStr,1,len(@ListStr)-1)
End
Then use that function as
Select Id,dbo.GetCSV(Id) from TestTable group by Id
Its as easy as simple query writing.
This works great! However with a small modification it can go a wee bit forward:
— Get CSV values
SELECT t.ID, STUFF(
(SELECT ‘,’ + s.Col
FROM TestTable s
WHERE s.ID = t.ID
FOR XML PATH(”), TYPE).value(‘.’, ‘VARCHAR(MAX)’),1,1,”) AS CSV
FROM TestTable AS t
GROUP BY t.ID
GO
Adding TYPE ensures that it is available as XML object and doing a type cast to varchar ensure that all the xml-sensitive characters would be converted back from XML. If one of the value was “Me & You” the output would be the same.
NIce! but I have a question.
What is FOR XML PATH(”) for ??
Hi
I like to GROUP BY a varchar not an int , what changes do I have to do,
Thanks
I am Getting This:
Conversion failed when converting the varchar value ‘H123’ to data type int.
Thanks
Nice Article!
How can I achieve the following. The columns are depend on the values of CSV
EX:
ID CSV
1 A
1 B
2 A
2 B
2 C
2 D
ID CSV1 CSV2 CSV3 CSV4
1 A B C
2 A B C D
Thanks
Sathish
i am also want this type of result for my table. pls help
Dear Pinal,
Solution You have provided is not working for SQL COMPACT EDITION 3.5.
Following is the Error Message:
“There was an error parsing the query. [ Token line number = 2,Token line offset = 8,Token in error = SELECT ]”
Please help on this issue.
Regards,
Bhavik
Are you using MySQL? This site is for SQL Server
It should be
FOR XML PATH(”)),1,2,”) AS CSV
and not
FOR XML PATH(”)),1,1,”) AS CSV
as it will leave a comma at the beginning.
This is great.. but now if the same was to come into one column than..
Col
1
A
B
C
2
A
B
C
D
E
declare @TestTable TABLE (ID INT, Col VARCHAR(4))
INSERT INTO @TestTable (ID, Col)
SELECT 1, ‘A’
UNION ALL
SELECT 1, ‘B’
UNION ALL
SELECT 1, ‘C’
UNION ALL
SELECT 2, ‘A’
UNION ALL
SELECT 2, ‘B’
UNION ALL
SELECT 2, ‘C’
UNION ALL
SELECT 2, ‘D’
UNION ALL
SELECT 2, ‘E’
SELECT COL FROM(
SELECT ID,CONVERT(VARCHAR(50),ID)COL
FROM @TestTable GROUP BY ID
UNION
SELECT ID,COL
FROM @TestTable)SRC ORDER BY SRC.ID
declare @TestTable TABLE (ID INT, Col VARCHAR(4))
INSERT INTO @TestTable (ID, Col)
SELECT 1, ‘A’
UNION ALL
SELECT 1, ‘B’
UNION ALL
SELECT 1, ‘C’
UNION ALL
SELECT 2, ‘A’
UNION ALL
SELECT 2, ‘B’
UNION ALL
SELECT 2, ‘C’
UNION ALL
SELECT 2, ‘D’
UNION ALL
SELECT 2, ‘E’
SELECT COL FROM(
SELECT ID,CONVERT(VARCHAR(4),ID)COL
FROM @TestTable GROUP BY ID
UNION
SELECT ID,COL
FROM @TestTable)SRC ORDER BY SRC.ID
How to put every value of CSV into another column ?
Nice, Its working fine.
Thank u.!!!
I was received this error
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ‘,’ to data type int.
This is my query
;WITH TEMP(STDWorkPlanID,STDCCCMapID)
AS (
SELECT WP.[STDWorkPlanID],
(
SELECT ‘,’+WPM.[STDCCCMapID] ———(This line error ‘,’ )
FROM [BHAVINI_CCC_STD].[dbo].[STD_CCC_WorkplanMapping] WPM
WHERE WPM.[STDWorkPlanID]= WP.[STDWorkPlanID]
FOR XML PATH(”)) AS[STDCCCMapID]
FROM [BHAVINI_CCC_STD].[dbo].[STDWorkPlan] WP
WHERE WP.[STDWorkPlanID]= WP.[STDWorkPlanID]
)
SELECT STDWorkPlanID,STDCCCMapID FROM TEMP WHERE STDCCCMapID >0;
i want output if input like this
ID CSV
1 A
1 B
1 A
1 B
2 A
2 A
2 B
2 C
2 D
2 D
ID CSV
1 A B C
2 A B C D
Please reply…
Bharat