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.
