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 Server Training Scripts

Comma delimited list creation in SQL Server T-SQL

The following T-SQL scripts demonstrate the building of comma delimited list:

 -- EXAMPLE 1 - Using XML PATH (SQL Server 2005 and on)

-- T-SQL create comma delimited list from single column result

SELECT ColorCommaDelimitedList =

Stuff((SELECT ', ' + Color AS [text()]

        FROM  

        (SELECT DISTINCT Color FROM AdventureWorks2008.Production.Product

         ) x

        For XML PATH ('')),1,1,'')

/*

 ColorCommaDelimitedList

 Black, Blue, Grey, Multi, Red, Silver, Silver/Black, White, Yellow

 */

------------

 

-- EXAMPLE 2 - Using XML PATH & CTE (SQL Server 2005 and on)

 

-- T-SQL create comma delimited list using CTE - Common Table Expression

;WITH cteColor AS

(SELECT DISTINCT Color FROM AdventureWorks2008.Production.Product)

 SELECT ColorCommaDelimitedList =

   Stuff((SELECT ', ' + Color AS [text()]

          FROM cteColor 

          For XML PATH ('')),1,1,'')

 /*

 ColorCommaDelimitedList

 Black, Blue, Grey, Multi, Red, Silver, Silver/Black, White, Yellow

 */

------------

 

-- EXAMPLE 3 - Using local variable (SQL Server 2000 and before)

 

-- T-SQL creating comma delimited list with local variable & multiple statements

USE AdventureWorks;

DECLARE @CommaLimitedList VARCHAR(MAX) = ''

SELECT @CommaLimitedList = Color + ', ' + @CommaLimitedList

FROM (SELECT DISTINCT Color FROM Production.Product WHERE Color is not null) x

SELECT CommaDelimitedList=@CommaLimitedList

GO

/*

CommaDelimitedList

Yellow, White, Silver/Black, Silver, Red, Multi, Grey, Blue, Black,

*/

 ------------

 

-- EXAMPLE 4 - Using XML PATH & correlated subquery for sublist

 

-- Create comma delimited sublist

SELECT   Subcategory = ps.[Name],

         ColorList = Stuff((SELECT DISTINCT  ', ' + Color AS [text()]

                            FROM AdventureWorks2008.Production.Product p

                            WHERE p.ProductSubcategoryID = ps.ProductSubcategoryID

                            FOR XML PATH ('')),1,1,'')

FROM     AdventureWorks2008.Production.ProductSubcategory ps

ORDER BY Subcategory;

GO

/*

Subcategory             ColorList

....

Helmets                 Black, Blue, Red

Hydration Packs         Silver

Jerseys                 Multi, Yellow

....

*/

------------

-- EXAMPLE 5 - Preparing spaces delimited list

-- T-SQL make spaces delimited list of ProductNumbers
SELECT Alpha.List.value('.','varchar(256)') AS DelimitedList
FROM   (SELECT   TOP ( 5 ) ProductNumber + '    '
        FROM     AdventureWorks2008.Production.Product
        ORDER BY ProductNumber DESC
        FOR XML PATH(''), TYPE) AS Alpha(List);
/*
DelimitedList
WB-H098    VE-C304-S    VE-C304-M    VE-C304-L    TT-T092   
*/

------------

Related article:

http://www.sqlusa.com/bestpractices2005/sublist/

 

The Best SQL Server Training in the World