In this article I will explain with an example, how to generate comma separated list from Table rows in SQL Server.
 
 

Database

I have made use of the following table Customers with the schema as follow.
Generate comma separated list from Table rows in SQL Server
 
I have already inserted few records in the table.
Generate comma separated list from Table rows in SQL Server
 
Note: You can download the database table SQL by clicking the download link below.
         Download SQL file
 
 

Generating comma separated list

Following are different ways to generate the comma separated list from Table rows in SQL Server.
1. Using STRING_AGG function.
2. Using COALESCE function.
3. Using FOR XML PATH function.
 

1. Using STRING_AGG function

STRING_AGG function concatenates the values of string expressions and places separator values between them.
Note: The separator isn't added at the end of string.
 
In the following example, the Name column values from Customers table are concatenated using comma separated value using STRING_AGG function.
For more details on STRING_AGG function refer here.
SELECT STRING_AGG([Name], ',') 
FROM [Customers]
 

2. Using COALESCE function

In the following example, the Name column values from Customers table are concatenated using comma separated value using COALESCE function.
For more details on COALESCE function refer here.
DECLARE @Name VARCHAR(MAX)
SELECT @Name = COALESCE(@Name + ',', '') + [Name]
FROM [Customers]
SELECT @Name
 

3. Using FOR XML PATH function

In the following example, the Name column values from Customers table are concatenated using comma separated value using FOR XML PATH function.
For more details on FOR XML PATH function refer here.
SELECT STUFF
(
  (SELECT ',' + [Name]
   FROM [Customers]
   FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),
   1, 2, ''
)
 
 

Screenshot

Generate comma separated list from Table rows in SQL Server
 
 

Downloads

Download Code