Execute the following example Microsoft SQL Server T-SQL scripts in Management Studio Query Editor to demonstrate how to create a comma-delimited (csv) list within an SQL query.

The FOR XML PATH clause creates the comma-limited sublist for each territory.

-- SQL xml path comma delimited list- SQL correlated subquery - sql comma delimited list

-- SQL create comma-limited list - sql comma separated list - mssql csv list

Select Territory=st.[Name],

SalesYTD = '$'+convert(varchar,floor(SalesYTD),1), -- SQL currency format

SalesStaffAssignmentHistory = Stuff((

      Select ', ' + c.FirstName+' '+c.LastName as [text()]

      From Person.Contact c

      Join Sales.SalesTerritoryHistory sth

            On c.ContactID = sth.SalesPersonID

      Where sth.TerritoryID = st.TerritoryID

      Order by StartDate

      For XML Path ('')), 1, 1, '')

From AdventureWorks.Sales.SalesTerritory st

Order by SalesYTD desc ;

Go

/*  Results

Territory   SalesYTD          SalesStaffAssignmentHistory

Northwest   $9,178,765.00     Shannon Elliott, Terry Eminhizer, Martha Espinoza

Southwest   $8,351,296.00     Shelley Dyck, Jauna Elson

Canada      $6,917,270.00     Carla Eldridge, Michael Emanuel, Gail Erickson

Central     $4,677,108.00     Linda Ecoffey, Maciej Dusza

France      $3,899,045.00     Mark Erickson

Northeast   $3,857,163.00     Maciej Dusza, Linda Ecoffey

United Kingdom    $3,514,865.00     Michael Emanuel

Southeast   $2,851,419.00     Carol Elliott

Germany     $2,481,039.00     Janeth Esteves

Australia   $1,977,474.00     Twanna Evans

*/

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

/* The following query produces identical results applying GROUP BY */

-- SQL group by csv list create - SQL min aggregate function

-- SQL stuff - SQL text() function

Select Territory=st.[Name],

SalesYTD = '$'+convert(varchar,floor(MIN(SalesYTD)),1),

SalesStaffAssignmentHistory = Stuff((

      Select ', ' + c.FirstName+' '+c.LastName as [text()]

      From Person.Contact c

      Join Sales.SalesTerritoryHistory sth

            On c.ContactID = sth.SalesPersonID

      Where sth.TerritoryID = st.TerritoryID

      Order by StartDate

      For XML Path ('')), 1, 1, '')

From AdventureWorks.Sales.SalesTerritory st

GROUP BY st.[Name], TerritoryID

Order by SalesYTD desc ;

Go
------------

-- Generating T-SQL comma separated list using a local variable

-- Requires multi-statements - cannot be imbedded in another query

USE AdventureWorks2008;

DECLARE @CommaLimitedList VARCHAR(MAX) = ''

SELECT @CommaLimitedList = Name + ', ' + @CommaLimitedList

FROM Production.Product

WHERE ProductID BETWEEN 800 and 810

SELECT @CommaLimitedList

GO

/*

HL Mountain Handlebars, ML Mountain Handlebars, LL Mountain Handlebars, HL Headset,

ML Headset, LL Headset, HL Fork, ML Fork, LL Fork, Road-550-W Yellow, 48,

Road-550-W Yellow, 44,

*/

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