Generating Comma Separated list in TSQL by using XML Path
Posted Under: Uncategorized
In one to many relationships of two tables, sometimes we need to write a query which can return comma seperated list of all child values for each parent. TSQL is not designed for string parsing and it does not scale well in string processing scenarios but luckily by using XML capabilities of SQL Server 2005 you can easily and efficiently generate comma separated values. Following example demonstrate the technique by using Model and Product tables of AdventureWorks database where a model can have multiple products and we need to list one row for each model along with the comma separated list of products for that model.
Use AdventureWorks
Go
SELECT PM.Name as Model
,(
SELECT P.Name + ‘,’
FROM
WHERE P.ProductModelID = PM.ProductModelID
ORDER BY P.Name
FOR XML PATH(”)
) AS Products
FROM
Reader Comments
Your solution is great, but it does leave a trailing comma. I guess you could use LEN and LEFT to cut it off.
This was really useful, thanks a lot. Here is the code to remove the trailing comma.
SELECT
Model,
CASE
WHEN LEN(Products) > 0 THEN LEFT(Products, LEN(Products) -1)
ELSE ”
END AS Products
FROM (
SELECT PM.Name as Model
,(
SELECT P.Name + ‘,’
FROM Production.Product AS P
WHERE P.ProductModelID = PM.ProductModelID
ORDER BY P.Name
FOR XML PATH(”)
) AS Products
FROM Production.ProductModel AS PM
) AS TBL
Great solution. Provides a new solution to two separate problems I’ve been dealing with. Thanks for sharing.
Thanks alot for your post Namwar.
It’s really great. It helped me alot.
-Thank you agan.
-Suneel.Eduru
Great Solution. Thanks for sharing.
use STUFF to get rid of the extra comma