Comma Separate a Table Column in SQL Server

In SQL Server, you can comma separate a table column using the FOR XML PATH method or the STRING_AGG function (available in SQL Server 2017 and later versions).

Syntax:

-- Using FOR XML PATH method 
SELECT STUFF(( SELECT ',' + column_name FROM table_name FOR XML PATH('') ), 1, 1, '') AS comma_separated_column; -- Using STRING_AGG function (SQL Server 2017 and later) 
SELECT STRING_AGG(column_name, ',') AS comma_separated_column FROM table_name; 

Example 1: Comma Separate a Column using FOR XML PATH

Let's say we have a table called colors with a column called color_name, and we want to comma separate the values in the column.

Table Data:

Query:

SELECT STUFF(( SELECT ',' + color_name FROM colors FOR XML PATH('') ), 1, 1, '') AS comma_separated_colors; 

Result:

The result will display the comma separated values of the color_name column.

Example 2: Comma Separate a Column using STRING_AGG (SQL Server 2017 and later)

Let's say we have a table called colors with a column called color_name, and we want to comma separate the values in the column using the STRING_AGG function.

Table Data:

Query:

SELECT STRING_AGG(color_name, ',') AS comma_separated_colors FROM colors; 

Result:

The result will display the comma separated values of the color_name column.