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