DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to create a csv/delimited list for groups?

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

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE