Comma Separated string of all the rows of a column


Often we need to create a comma separated string from all the row values of a column of a table. For example, a table named Product has the following rows:

  Product_ID Product_Name
1 14458 MAXALT
2 14464 COSOPT
3 15255 SINGULAIR
4 16641 FORTZAAR
5 20570 ARCOXIA
6 20850 EZETROL
7 23343 FOSAVANCE
8 25443 JANUMET/JANUVIA

Now we want a comma separated string of all the product names. So our desired output is:

MAXALT, COSOPT, SINGULAIR, FORTZAAR, ARCOXIA, EZETROL, FOSAVANCE, JANUMET/JANUVIA

The first algorithm that may come to our mind is using cursor. We declare a cursor selecting all the product names:

select Product_Name
from Products

then we loop through the cursor and concatenate the product names. But the desired output can be found in a very easy and faster way. The thing we do using cursor can be left to the sqlserver engine. Sql server itself will produce the concatenated product names for us.

DECLARE @all_product AS varchar(MAX)

SET @all_product = ''
SELECT @all_product = @all_product + ', ' + Descr_Product
FROM dbo.Products

SELECT @all_product

 

Thats it.

The heck here is: sqlserver assigns the product name to the variable @all_product. For each of the rows it tries to concat the Product_name with the value that is already in the variable @all_product. Just like a recursion. The benefit here is, it is much faster than the cursor and also its done by sqlserver engine.

The above code will not give the exact output that we want. It will have an extra comma (,) at the starting of the concatenated string. You can easily avoid this by using the coalesce function. The final code is as follows.

DECLARE @all_product AS varchar(MAX)

SET @all_product = null
SELECT @all_product = coalesce(@all_product + ', ' + Descr_Product, Descr_Product)
FROM dbo.Products

SELECT @all_product