How to Create a Comma Separated List with SELECT in SQL Server: A Comprehensive Guide by SQLYoga
                     WWW.SQLYOGA.CO
M
CREATE
COMMA 
SEPARATED 
LIST WITH 
SELECT
IN SQL 
SERVER
GENERATE COMMA SEPARATED 
LIST WITH SELECT 
STATEMENT
Today, I encountered a situation where 
I needed to display all related data in a 
comma-separated list using a  SELECT 
statement. Up until now, I have been 
using a scalar function that utilizes 
COALESCE() to achieve this. However, I 
discovered a fantastic solution that 
allows us to generate a comma-
separated list without needing a scalar 
function. Let's dive into it.
www. sqlyoga. 
com
EXAMPLE 
SCoCnsiEdeNr thAe Rfollowing table: IO:
CREATE TABLE #test( 
field1 VARCHAR(5),
field2 VARCHAR(5)
);
www. sqlyoga. 
com
Lets insert some data in this 
table:
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'
www. sqlyoga. 
com
CURRENT DATA IN THE 
ATfAter BinsLeErting the data, our table looks like this:
www. sqlyoga. 
com
EXPECTED OUTPUT
We want to generate a comma-separated list of field2 for each 
unique
field1. The expected output is:
www. sqlyoga. 
com
PROPOSED 
HSeOre's the SQL query to achieve the desired outpuLt:UTION
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 
wwwG.R sOqUlPy oBgYa .  
com
field1
PROPOSED 
HSeOre's the SQL query to achieve the desired outpuLt:UTION
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 
wwwG.R sOqUlPy oBgYa .  
com
field1
EXPLANATION
1.Subquery with FOR XML PATH: The 
subquery concatenates field2 values into 
a  single string separated by commas for 
each field1. The FOR XML PATH('') clause 
converts the result into XML format, 
which we then transform into a  plain 
string.
2.Substring Function: The SUBSTRING function 
removes the leading comma and space 
from the concatenated string. The 3 
indicates starting from the third character, 
effectively skipping the first two 
characters (, ).
3.Group By: The GROUP BY clause ensures 
wwww. sqely ogag.  et one row per field1.
com
OUTPUT
Running the above query will yield the following 
output:
www. sqlyoga. 
com
With  this  approach,  you  can  generate  a   
comma- separated  list  without  relying  on  scalar 
 functions. This method is efficient and leverages 
SQL Server's XML capabilities to concatenate 
strings.
Read  More  about  advanced  SQL  techniques  
and optimizing your queries on our blog.
www. sqlyoga. 
com
WAS 
HTHELISPFUL
Be s?ure to save  this post so  you can  come  back  to it later!
WWW.SQLYOGA.CO
M