MS SQL Server query to return a comma-separated string from a table

By | 3rd August 2019

In the other article, we saw the usage of Table variables. In this article, we will see how to transform column values in a table to a comma-separated string.

Let’s create a table variable “Teams” with the columns ‘ID’ and ‘Team’ (use the below script).

DECLARE @Teams TABLE (ID INT IDENTITY(1,1), Team VARCHAR(50));

INSERT INTO @Teams VALUES ('India'),('Australia'),('England'),('New Zealand'),('Pakistan'),('Sri Lanka'),('South Africa'),('Bangladesh');

When you fire a SELECT over the above table, it will return the result as shown below

Query to get comma-separated values

There are actually two ways to get comma separated values from the above table.

Solution 1

DECLARE @A VARCHAR(1000) 
SELECT @A = COALESCE(@A + ',' + Team, Team) FROM @Teams 
SELECT @A as CommaSepStr

Solution 2

DECLARE @A VARCHAR(1000) 
SELECT @A = COALESCE(@A + ',', '') + Team FROM @Teams 
SELECT @A as CommaSepStr

Upon executing any of the above two queries, we will get the result as shown below.

Leave a Reply

Your email address will not be published. Required fields are marked *