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 create list results using CROSS APPLY?

Execute the following SQL Server T-SQL script in SSMS Query Editor to generate a comma separated order list result for each mountain bike dealer:

-- T-SQL create comma-delimited list with CROSS APPLY

USE AdventureWorks;

 

SELECT   Dealer = st.Name,

         [SalesOrders] = LEFT(o.list,LEN(o.list) - 1)

FROM     Sales.Customer c

         INNER JOIN Sales.Store st

           ON c.CustomerID = st.CustomerID

         CROSS APPLY (SELECT   CONVERT(VARCHAR(14),SalesOrderID) + ',' AS [text()]

                      FROM     Sales.SalesOrderHeader s

                      WHERE    s.CustomerID = c.CustomerID

                      ORDER BY SalesOrderID

                      FOR XML PATH('')

                     ) o(list)

ORDER BY st.Name;

 

/*

Dealer                        SalesOrders

Aerobic Exercise Company      53596,59030,65287,71915

Affordable Sports Equipment   43912,44564,45339,46101,47056,48080,49160,50313

All Cycle Shop                46646,47695,48742,49847,51164,55273,61256,67337

All Seasons Sports Supply     51142,55296,61251,67326

Alpine Ski House              44548,45324,46085,47035,48055,49141,50300

Alternative Vehicles          44483,45267,46025

Amalgamated Parts Shop        51169,55309,61263,67324

Another Bicycle Company       53514,58961,65234,71833

*/

 

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