Blog Detail

SQL SERVER: Generate Comma Separated List with SELECT statement

Today I have the following situation, where I need to display all related data in comma separated list. 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.

Example:
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

Expected Output:
Get Comma separated List

Proposed Solution:
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

About me

User

Tejas Shah

Microsoft Certified Professional Expert. I have 14+ years of experience in Design and Develop 100+ .Net applications using Asp.Net, C#, Asp.Net Core, VB.Net, SQL Server, MVC, AngularJS, JavaScript, Azure, HTML5 and CSS3.

Recent Post