datetime tune date into pad dynamic cursor money percent sp job isnumeric while over update
FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS
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