Comma Separated Values of a Column using SQL

We have been several times in to the scenario where we wish if we could retrieve the values from a column as a comma separated list or with any other delimiter. One of my colleagues reached out to me asking about if there is an easy way of doing this. She tried various things but the result was still not in the format as she was looking for. So, let’s see how this can be done using T-SQL.

Suppose this is the query from where we want to get the content of “id” column as a comman separated list.

SELECT TOP 5 * FROM sys.sysobjects

This can easily be done by using FOR XML PATH as follows –

SELECT TOP 5 id FROM sys.sysobjects
FOR XML PATH ('')

This gives you something as above but probably that you were not interested in as it gives you the output as tags of XML. Let’s refine it further –

SELECT TOP 5 ', ' + CAST(id AS VARCHAR) FROM sys.sysobjects
FOR XML PATH ('')

Cool. Concatenating the column with a delimiter removes the tags but the output is still coming in as hyperlink, not an exact list of comma separated values. Let’s do something more –

SELECT * FROM
(
	SELECT TOP 5 ', ' + CAST(id AS VARCHAR) FROM sys.sysobjects
	FOR XML PATH ('')
)A (id)

Converting this into a derived table and querying it with a named column does the trick further and you get it similar to what exactly you were looking for. Wait, wait, wait! You see an additional comma at the beginning of it. In fact not an additional comma but since we concatenated the result of “id” column by prefixing it with a comman and hence it is there. This can easily be replaced by any string manipulatation function. Let’s use STUFF function to get rid of it –

SELECT id, STUFF(id, 1, 2, '') FROM
(
	SELECT TOP 5 ', ' + CAST(id AS VARCHAR) FROM sys.sysobjects
	FOR XML PATH ('')
)A (id)

This gives you the perfect output. You can achieve the same output by using a CTE style syntax also –

;WITH CTE (id) AS
(
	SELECT TOP 5 ', ' + CAST(id AS VARCHAR) FROM sys.sysobjects
	FOR XML PATH ('')
)
SELECT id, STUFF(id, 1, 2, '')  FROM CTE

Moreover, if you are using SQL Server 2017 or later, there is an in-built function STRING_AGG() to achieve the same purpose without using any XML path queries –

;WITH CTE (id) AS
(
	SELECT TOP 5 id FROM sys.sysobjects
)
SELECT STRING_AGG(id, ',') AS id FROM CTE

There are many other ways to get the comma/delimited separated values from a column but the methods that we saw here are the most common ways and in fact, availability of an in-built function has really made the things easy.