DEV Community

HOW TO GENERATE COMMA SEPERATED STRING USING STUFF FUNCTION IN TRANSACT-SQL

Benjamin Fadina on May 24, 2023

Concepts Covered In This Video Include:- How To Connect To SQL SERVER using management studio. 2.How to create a new database using managemen...
Read full post
Collapse
 
notte profile image
Olesia Dudareva •

Actually, STUFF() doesn't generate anything. It just replaces the first comma in the string with the emptiness. The string is generated by FOR XML.

Anyway, this approach is very good and I use it quite often. But SQL Server version 2017 and higher has another very good function STRING_AGG()
Just for information, because your version is 2016.

For instance your example will be looking like the code below:

SELECT STRING_AGG(ProductName,',') as list_of_products
FROM Products
WHERE (CategoryID = @CategoryID)
Enter fullscreen mode Exit fullscreen mode

This is the result:

list_of_products
Cream,Milk,Yogurt

Thanks @rozhnev for your great tool (not sponsored)! I just have SQL Server V.2014 on my machine and it's difficult to show new functionality using it.