Till today we are using scalar function to display Comma separated list with select statement. That scalar function use the COALESCE() to make comma separated list. Today I found wonderful solution to display comma separated list without scalar function. Let see that.
Scenario:
I have Table like:
CREATE TABLE #test(
field1 VARCHAR(5), field2 VARCHAR(5)
)
Lets insert some data in this table:
INSERT INTO #test
SELECT '001','AAA'
UNION ALL
SELECT '001','BBB'
UNION ALL
SELECT '002','CCC'
UNION ALL
SELECT '003','DDD'
UNION ALL
SELECT '004','EEE'
UNION ALL
SELECT '004','FFF'
UNION ALL
SELECT '004','GGG'
So now my table has Data like:
- Get Comma separated List
I want output like:
- Get Comma separated List
I come up with very good solution. Let me share with all of you:
SELECT field1,
SUBSTRING(
(
SELECT ( ', ' + field2)
FROM #test t2
WHERE t1.Field1 = t2.Field1
ORDER BY t1.Field1, t2.Field1
FOR XML PATH('')
), 3, 1000)
FROM #test t1
GROUP BY field1
- Get Comma separated List
My Output will be:
- Get Comma separated List
Please make comments, if this helps you in any way
30 comments: