FOR XML PATH IN SQL

FOR XML PATH in SQL

We use FOR XML PATH in sql to get comma separated values   from tables have one to many relation.

For example we have two tables tblCategory and tblProduct and one category can have multiple products. Now we want to get all products of a category in comma separated in single columns then we can use FOR XML PATH

tblCategory

CategoryID	CategoryName
1	Electronics
2	Apparels

tblProduct

ProductID	ProductName	Cost	CategoryID
1	Laptop	45000	1
2	LCD TV	40000	1
3	DVD Player	5000	1

Use this query

SELECT     CategoryName, STUFF

                          ((SELECT     ',' + Product .ProductName

                              FROM         tblProduct  Product

                              WHERE     (CategoryID = Category.CategoryID) FOR XML PATH('')), 1, 1, '') AS Products

FROM         tblCategory  Category

CategoryName	Products
Electronics	Laptop, LCD TV,DVD Player