Q:

Subquery or conditional logic for result set

Consider the mock tables
ORDERS                          
OrderID CustID date              
10       1     2014-01-01       
11       3     2014-02-01       
12       2     2014-03-01        

CUSTOMER 
CustID FName LName
1      Jon   Doe         
2      Jane  Doe
3      Mike  Brown

LINEITEM                        
OrderID ProdID                  
10      1                        
10      3                       
11      2                       
12      1
12      2

PRODUCTS
ProdID Description 
1      Apple
2      Orange
3      Grape

I want to return a result set that looks like this:
OrderID CustomerLastName Apple Orange Grape
11      Brown            No    Yes    No
12      Doe              Yes   Yes    No

Logic: Select orderID, customer last name, determine if they bought each product (yes or no)
       for all orders with order date greater then 2014-01-01.
This is about as far as I can get
select O.OrderID as 'OrderID', 
       C.LName as 'CustomerLastName', 
      (some conditional or subquery for figuring out yes or no) as 'Apple',
      (some conditional or subquery for figuring out yes or no) as 'Orange',
      (some conditional or subquery for figuring out yes or no) as 'Grape'
from ORDERS O join CUSTOMER C using (CustID)
              join LINEITEM using (OrderID)
              join Products P using (ProdID)
where O.date > 2014-01-01;

Any help on how to get just one line returned for each order and YES / NO logic for the products would be greatly appreciated.
Thanks

A:

to what I was eluding in my comment I always would 'fake' a pivot table using MAX(then a conditional statement) if you want to play around with it :)
SELECT 
    O.OrderID, C.LName AS CustomerLastName,
    MAX(CASE WHEN P.Description = 'Apple' THEN 'Yes' ELSE 'No' END) AS Apple,
    MAX(CASE WHEN P.Description = 'Orange')THEN 'Yes' ELSE 'No' END) AS Orange,
    MAX(CASE WHEN P.Description = 'Grape')THEN 'Yes' ELSE 'No' END) AS Grape
FROM ORDERS AS O
JOIN CUSTOMER AS C USING (CustID)
JOIN LINEITEM AS L USING (OrderID)
JOIN Products AS P USING (ProdID)
WHERE O.date > '2014-01-01'
GROUP BY O.OrderID, C.CustID

