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 architect a sublist with XML path?

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,

*/

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

 

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