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
......
*/
|