1

Here is script of 3 table:

create table products(
pid varchar(10),
pname varchar(20));

create table [Order Details](
oid varchar(10),
pid varchar(10),
quantity int);

create table orders (
oid varchar(10), 
oname varchar(10));

insert into [Order Details]
select 'o1', 'p1', 10
union all
select 'o1', 'p2', 20
union all
select 'o1', 'p3', 20
union all
select 'o2', 'p1', 10
union all
select 'o2', 'p2', 10
union all
select 'o3', 'p1', 10

insert into products
select 'p1', 'Product A'
union all
select 'p2', 'Product B'
union all
select 'p3', 'Product C'

insert into orders
select 'o1', 'Order #1'
union all
select 'o2', 'Order #2'
union all
select 'o3', 'Order #3'

select * from products;
select * from orders;
select * from [Order Details];

And here is my query:

select oname, pname, quantity
from orders o1 inner join [Order Details] o2 on o1.oid=o2.oid
inner join products p on o2.pid=p.pid
group by oname, pname, quantity

The output is like:

Order Name  Product Name Quantity
Order #1    Product A    10
Order #1    Product B    20
Order #2    Product A    10

I need to use syntax stuff (select... for XML PATH) for reporting likes that:

Order Name  Product Name           Quantity
Order #1    Product A, Product B   30
Order #2    Product A              10

Because there are 3 tables (I have searched but the examples just have 2 table only). So I have tried many times but the result is always failed. I'm embarrassed with the rest table#3... Please help me, thanks so much!

3

Based off of your examples queries, this should work out for you (Comments Included):

-- works
SELECT      o1.oname AS [Order Name],
            (SELECT (STUFF(

                -- Here is the XML concatenation.
                (SELECT 
                            ', ' + p.pname 
                FROM        dbo.products AS p 
                INNER JOIN  dbo.[Order Details] AS [od1] ON p.pid = od1.pid --Join the products to the Order Details...
                WHERE od1.oid = o1.oid  -- Only for the "main" orders
                FOR XML PATH(''))

                    , 1, 2, ''))) AS [Product Name], -- remove the first comma and space.
            SUM(o2.quantity) AS [Quantity]
FROM        dbo.orders AS [o1]
INNER JOIN  dbo.[Order Details] AS [o2] ON o1.oid = o2.oid
GROUP BY    o1.oname, o1.oid;

However, your expected output doesn't have any Product C so maybe you don't want to show them. In which case, you would need to join back and restrict it away:

-- without Product C?
SELECT      o1.oname AS [Order Name],
            (SELECT (STUFF(

                -- Here is the XML concatenation.
                (SELECT 
                            ', ' + p.pname 
                FROM        dbo.products AS p 
                INNER JOIN  dbo.[Order Details] AS [od1] ON p.pid = od1.pid 
                WHERE od1.oid = o1.oid
                AND p.pid <> 'p3'
                FOR XML PATH(''))

                    , 1, 2, ''))) AS [Product Name],
            SUM(o2.quantity) AS [Quantity]
FROM        dbo.orders AS [o1]
INNER JOIN  dbo.[Order Details] AS [o2] ON o1.oid = o2.oid

-- Add this join to remove Product C.
INNER JOIN  dbo.products AS [p1] ON p1.pid =o2.pid
                AND p1.pid <> 'p3'

GROUP BY    o1.oname, o1.oid;

But then you're expected output doesn't have Order 3, so you'd need to restrict that away as well. However I'm not going to do that as you should have the info you need to do that yourself.

Best of luck!

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.