Menu

Want to work with us? We're on the lookout for digital experts.

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.

This site uses essential cookies for parts of the site to operate and have already been set. Find out more about how we use cookies and how you may delete them. You may delete cookies, but parts of the site will not work.