SQLTeam.com | Weblogs | Forums

Grouping help

I have a database of orders and order details. Order table just holds general header level data and the details includes all the items on the order.

I want to group the details table and get a yes / no for if an item is on the order. I have the information below:

orders table:
ID, NAME, REP
1, Jon, Bill
2, Jarod, Jarod

detail table:
ORDER ID, PRODUCT NAME, PRODUCT TYPE
1, CUP, 1
1, PLATE, 3
1, BOWL, 3
2, CHAIR, 2
2, PLATE, 3
2, BOWL, 3

I want to know (yes/no) if an item type is on an order. I want results like this;
ID, NAME, REP, TYPE1, TYPE2, TYPE3
1, Jon, Bill, Yes, No, Yes
2, Jarod, Jarod, No, Yes, Yes

I started a query like so, but I do not know how to accomplish my results.

SELECT 
orders,id, 
orders.name,
CASE WHEN detail.type = 1 THEN "Yes" ELSE "No" END
CASE WHEN detail.type = 2 THEN "Yes" ELSE "No" END
CASE WHEN detail.type = 3 THEN "Yes" ELSE "No" END
FROM orders
LEFT JOIN details ON orders.id = details.id
GROUP BY details.id
SELECT o.ID, o.NAME, o.REP, D.TYPE1, D.TYPE2, D.TYPE3
FROM (
    SELECT id,
        MAX(CASE WHEN type = 1 THEN 'Yes' ELSE 'No' END) AS TYPE1,
        MAX(CASE WHEN type = 2 THEN 'Yes' ELSE 'No' END) AS TYPE2,
        MAX(CASE WHEN type = 3 THEN 'Yes' ELSE 'No' END) AS TYPE3
    FROM dbo.details
    GROUP BY id
) AS d
INNER JOIN dbo.orders o ON o.id = d.id