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 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to create list results using XML PATH?

Execute the following SQL Server T-SQL script in SSMS Query Editor to generate a comma separated list result:

-- T-SQL comma-delimited list of SalesOrderID-s for each bike dealer

-- SQL correlated subquery: WHERE soh.CustomerID = c.CustomerID

USE AdventureWorks;

 

SELECT   s.Name,

         SalesOrderIDs = REPLACE((SELECT   SalesOrderID AS [data()]

                                  FROM     Sales.SalesOrderHeader soh

                                  WHERE    soh.CustomerID = c.CustomerID

                                  ORDER BY SalesOrderID

                                  FOR XML PATH ('')

                                 ),' ',',')

FROM     Sales.Customer c

         INNER JOIN Sales.Store s

           ON c.CustomerID = s.CustomerID

ORDER BY s.Name;

/*

Name                    SalesOrderIDs

......

Ace Bicycle Supply      46982,48007,49056,51801,63208,69428

Action Bicycle Spe      53458,58908,65158,71784

Active Cycling          48377,49536

Active Life Toys        47398,48339,49496,50689

Active Systems          46656,47707,49880

Active Transport Inc.   43876,44522,45302,46059,50278,51743,57114,69438

Activity Center         46654,47703,48777,49878,51158,55313,61257,67331

......

*/

 

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