Create A Comma Delimited List From a Column

In this article I will try to show you a simple example on how to Create a Comma Delimited List from a column also called as CSV (Comma Separated Values).

For example here is our Sample Table -

Id	Names
1	A
1	B
1	C
2	A
2	B
3	X
3	Y
3	Z

And here is the expected output -

Id	AllNames
1	A, B, C
2	A, B
3	X, Y, Z

Though in above example I have used Comma, sometimes it may required to use other delimiters as well, like Single space (A B C) or Vertical bar (A | B | C) or may be some other delimiter.

Create Sample Data -

--Create sample table CREATE TABLE Test ( Id INT, Names VARCHAR(100) ) GO -- Load sample data INSERT INTO Test 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 3,'X' UNION ALL SELECT 3,'Y' UNION ALL SELECT 3,'Z' GO

SQL Server 2005 / SQL Server 2008 Solution for Comma delimiter.
1st Solution -

SELECT T1.Id             ,AllNames = SubString (( SELECT ', ' + T2.Names FROM Test as T2 WHERE T1.Id = T2.Id FOR XML PATH ( '' ) ), 3, 1000) FROM Test as T1 GROUP BY Id

If the column “Names” doesn’t contain any spaces in between its values then here is another solution -

2nd solution -

SELECT T1.Id            ,AllNames = REPLACE (( SELECT T2.Names AS 'data()' FROM Test as T2 WHERE T1.Id = T2.Id FOR XML PATH ( '' ) ), ' ', ', ') FROM Test as T1 GROUP BY Id

Advantage with 2nd solution is that if the data type of column whose value you want to concatenate is INT or any other noncharacter data type you don’t need to CONVERT it into varchar data type. In 1st solution you will need to convert the column to Varchar if its original data type is numeric. But 2nd solution will fail in case there are some in-between spaces in column values.

Now if you want to create the list using other delimiter, replace comma from above query with your required delimiter.

E.g. for Vertical Bar

SELECT T1.Id             ,AllNames = SubString (( SELECT '| ' + T2.Names FROM Test as T2 WHERE T1.Id = T2.Id FOR XML PATH ( '' ) ), 3, 1000) FROM Test as T1 GROUP BY Id

Mangal Pardeshi

SQL MVP