SQL Query - Convert data from rows into comma separated string value - FOR XML PATH and STUFF

 Please watch the below YouTube video for complete explanation -

https://www.youtube.com/watch?v=aDH4X2nh9-8

Please find below the SQL Query -

-- Tables are from AdventureWorksDW database from Microsoft SQL Server.

Select EnglishProductCategoryName, 

stuff((Select ',' + EnglishProductSubCategoryName from DimProductSubCategory P2

WHERE P1.ProductCategoryKey =  P2.ProductCategoryKey

FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,1,'') as ProductSubCategoryName 

FROM  DimProductCategory P1