Want to work with us? We're on the lookout for digital experts.
We're hiringComma Separated List SQL
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;