SQLTeam.com | Weblogs | Forums

Combine Fields from Multi-JOIN

#1

I have the following QUERY:
SELECT
c.CustomerName as Customer,
o.OrderId as 'Order Number',
e.FirstName || ' ' || e.LastName as Employee,
p.ProductName as Product,
d.Quantity as Quantity,
(SELECT sum(d.Quantity) FROM OrderDetails AS d WHERE d.OrderId=o.OrderId) as Total
FROM
Customers AS c
INNER JOIN Orders AS o ON c.CustomerID = o.CustomerID
INNER JOIN OrderDetails AS d ON o.OrderID = d.OrderID
INNER JOIN Products AS p on d.ProductID = p.ProductID
INNER JOIN Employees AS e ON e.EmployeeID = o.EmployeeID;
On the TRY-Out Example of W3School it produces a result like this:
|Customer|Order Number|Employee|Product|Quantity|Total|
|Wilman Kala|10248|Steven Buchanan|Queso Cabrales|12|27|
|Wilman Kala|10248|Steven Buchanan|Singaporean Hokkien Fried Mee|10|27|
|Wilman Kala|10248|Steven Buchanan|Mozzarella di Giovanni|5|27|
...
Now I want someting like this:
|Wilman Kala|10248|Steven Buchanan|Queso Cabrales, Singaporean Hokkien Fried Mee, Mozzarella di Giovanni|12, 10, 5|27|
I tried LISTAGG and STUFF but the Server either doesn't support this (for inner SELECTS) or I got the Syntax completely wrong, I have also seen this TREAD here "SQL Combined Multiple rows into multiple columns" but are unsure how to apply it to my problem.

#2

This is a SQL Server forum. That code is definitely not from SQL Server. Someone here might be able to help, but you also might have better luck on a forum for the particular dbms you are using.