Concatenating Row Values in Transact-SQL

Copied from https://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

 

SELECT DISTINCT CategoryId
    ,ProductNames
FROM Northwind.dbo.Products p1
CROSS APPLY (
    SELECT ProductName + ','
    FROM Northwind.dbo.Products p2
    WHERE p2.CategoryId = p1.CategoryId
    ORDER BY ProductName
    FOR XML PATH('')
    ) D(ProductNames)
;