Concatenating column values from multiple rows.
Northwind, as always 🙂
From this
to this
WITH CTE ( CategoryId, product_list, product_name, length )
AS ( SELECT CategoryId, CAST( ” AS VARCHAR(MAX) ), CAST( ” AS VARCHAR(MAX) ), 0
FROM Northwind..Products
GROUP BY CategoryId
UNION ALL
SELECT p.CategoryId, CAST( product_list +
CASE WHEN length = 0 THEN ” ELSE ‘, ‘ END + ProductName AS VARCHAR(MAX) ),
CAST( ProductName AS VARCHAR(MAX)), length + 1
FROM CTE c
INNER JOIN Northwind..Products p
ON c.CategoryId = p.CategoryId
WHERE p.ProductName > c.product_name )
SELECT CategoryId, product_list
FROM ( SELECT CategoryId, product_list,
RANK() OVER ( PARTITION BY CategoryId ORDER BY length DESC )
FROM CTE ) D ( CategoryId, product_list, rank )
WHERE rank = 1 ;
Centurion 