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

Contents hide

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.