Execute the following
T-SQL example script in Microsoft SQL Server Management Studio Query Editor to demonstrate the creation of a csv list of products for each CategoryID.
USE Northwind;
-- Find the highest member number - SQL CASE FUNCTION - T-SQL correlated subquery
-- Select max(Products) from (select Products=count(*) from Products
-- group by CategoryID) a
SELECT CategoryId, ProductList=
MAX( CASE IdNumber WHEN 1 THEN ProductName ELSE '' END ) + ', '+
MAX( CASE IdNumber WHEN 2 THEN ProductName ELSE '' END ) + ', '+
MAX( CASE IdNumber WHEN 3 THEN ProductName ELSE '' END ) + ', '+
MAX( CASE IdNumber WHEN 4 THEN ProductName ELSE '' END ) + ', '+
MAX( CASE IdNumber WHEN 5 THEN ProductName ELSE '' END ) + ', '+
MAX( CASE IdNumber WHEN 6 THEN ProductName ELSE '' END ) + ', '+
MAX( CASE IdNumber WHEN 7 THEN ProductName ELSE '' END ) + ', '+
MAX( CASE IdNumber WHEN 8 THEN ProductName ELSE '' END ) + ', '+
MAX( CASE IdNumber WHEN 9 THEN ProductName ELSE '' END ) + ', '+
MAX( CASE IdNumber WHEN 10 THEN ProductName ELSE '' END ) + ', '+
MAX( CASE IdNumber WHEN 11 THEN ProductName ELSE '' END ) + ', '+
MAX( CASE IdNumber WHEN 12 THEN ProductName ELSE '' END ) + ', '+
MAX( CASE IdNumber WHEN 13 THEN ProductName ELSE '' END ) + ', '+
MAX( CASE IdNumber WHEN 14 THEN ProductName ELSE '' END )
FROM ( SELECT p1.CategoryId, p1.ProductName, Items=
( SELECT COUNT(*)
FROM Products p2
WHERE p2.CategoryId = p1.CategoryId
AND p2.ProductName <= p1.ProductName )
FROM Products p1 ) D ( CategoryId, ProductName, IdNumber )
GROUP BY CategoryId ;
GO
/* Partial results
CategoryId ProductList
1 Chai, Chang, Chartreuse verte, Côte de Blaye, Guaraná Fantástica, Ipoh Coffee,
2 Aniseed Syrup, Chef Anton's Cajun Seasoning, Chef Anton's Gumbo Mix, Genen
3 Chocolade, Gumbär Gummibärchen, Maxilaku, NuNuCa Nuß-Nougat-Creme, Pavlova,
4 Camembert Pierrot, Flotemysost, Geitost, Gorgonzola Telino, Gudbrandsdalsost,
5 Filo Mix, Gnocchi di nonna Alice, Gustaf's Knäckebröd, Ravioli Angelo,
6 Alice Mutton, Mishi Kobe Niku, Pâté chinois, Perth Pasties, Thüringer
7 Longlife Tofu, Manjimup Dried Apples, Rössle Sauerkraut, Tofu, Uncle
------------
Related articles:
How to architect a sublist with XML path
SQL SERVER – Create a Comma Delimited List Using SELECT Clause From Table Column
|
