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