Getting Comma separated value for table reference column is easy TIP #40

 

Problem :-

Most of the time we have one master table and one child table which content reference of master table. We have to fetch all the child  column as comma separated  for master table column. for example  I have a product category table and product table.  We want all the product as a comma separated in one column corresponding to each category.

Solution:-

lets understand this by an example. I am taking Adventureworks2012 database in the example.  below is default view

Now after processing we want below result

Now to achieve this we will write following command

DECLARE @CategoryProducts AS TABLE (ProductCategoryId INT,
Category VARCHAR(500),
Products VARCHAR(5000))

INSERT INTO @CategoryProducts(ProductCategoryId,Category )
SELECT pc.ProductCategoryID,pc.Name
FROM Production.ProductCategory pc

UPDATE tmp
SET Products = (SELECT p.Name + ','
                FROM  Production.Product p
                WHERE p.ProductSubcategoryID = tmp.ProductCategoryId
                ORDER BY p.Name
                FOR XML PATH (''))
FROM @CategoryProducts tmp

SELECT * FROM @CategoryProducts WHERE Products IS NOT NULL

 

So using XML path it easy to get what we want.

I hope this may help you somewhere

Enjoy!!!

RJ