Menu

Want to work with us? We're on the lookout for a talented Digital Project Manager.

We're hiring

Comma Separated List SQL

Web Bureau


30 August 2013 by Web Bureau

I had the following table structure

Column One Column Two
1 1
1 2
1 3
1 4
1 5
2 3
2 4
2 5
2 6
2 18

I wanted to retrieve the column two is a comma separated list as below:

Column One Comma List
1 1,2,3,4,5
2 3,4,5,6,18

This is the  script I used to perform such a task:

 

WITH CTE AS

 

(
SELECT DISTINCT
c1
FROM t1
)
SELECT c1,
CommaList = STUFF((
SELECT ',' + cast(c2 AS varchar(10))
FROM t1
WHERE t1.c1 = CTE.c1
ORDER BY t1.c2
FOR XML PATH(''),
TYPE).value('.','varchar(max)'),1,1,'')
FROM CTE
ORDER BY cte.c1;

Grow your businessStart a project with us today.