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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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