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 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to create a comma delimited list?

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

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

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

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

 */

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

 

-- 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

 */

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

 

-- Using COALESCE and local variable (SQL Server 2000 and before)

DECLARE  @YearList VARCHAR(MAX)

 

SELECT   @YearList = COALESCE(@YearList + ', ','') + CAST(OrderYear AS VARCHAR(4))

FROM     (SELECT DISTINCT OrderYear = YEAR(OrderDate)

          FROM   AdventureWorks2008.Sales.SalesOrderHeader) x

ORDER BY OrderYear

 

SELECT YearList = @YearList

/* YearList

2001, 2002, 2003, 2004   */

----------

-- 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,

*/

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

 

-- 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

....

*/

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

-- 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: Concatenating Row Values in Transact-SQL

 

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