CODEWITHSUNDEEP
sqlsql-serverjoincommon-table-expressionouter-join
mneill
mneill

Reputation: 43

SQL Server : display distinct list of orders, and indicate which orders contain specific products

I have a table that contains a lot of data, but the relevant data in the table looks something like this:

Orders table:

+----------+-----------+---------------+
| OrderID  | Product   |    Date       |
+----------+-----------+---------------+
|    1     | Apple     |  01/01/2001   |
|    1     | Pear      |  01/01/2001   |
|    1     | Pear      |  01/01/2001   |
|    1     | Orange    |  01/01/2001   |
|    1     | Pineapple |  01/01/2001   |
|    2     | Cherry    |  02/02/2002   |
|    2     | Cherry    |  02/02/2002   |
|    3     | Orange    |  03/03/2003   |
|    3     | Apple     |  03/03/2003   |
|    3     | Cherry    |  03/03/2003   |
+----------+-----------+---------------+

I'd like a query to return a distinct list of orders, and if the order contains certain products, to indicate as such:

+----------+-----------+--------+-------+
| OrderID  |   Date    | Apple? | Pear? |
+----------+-----------+--------+-------+
|    1     |01/01/2001 |    X   |   X   |
|    2     |02/02/2002 |        |       |
|    3     |03/03/2003 |    X   |       |
+----------+-----------+--------+-------+

Here's where I've left off and decided to seek out help:

WITH CTEOrder AS
(
   SELECT
       OrderID, Product, Date,
       ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY OrderID ASC) AS OrderRN
   FROM 
       Orders
)
CTEApple as 
(
    SELECT
        OrderID, Product, Date,
        ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY OrderID ASC) AS AppleRN
    FROM 
        Orders
    WHERE 
        Product = 'Apple'
),
CTEPear
(
    SELECT
        OrderID, Product, Date,
        ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY OrderID ASC) AS PearRN
    FROM 
        Orders
    WHERE 
        Product = 'Pear'
)
SELECT
    o.OrderID, o.Product, o.Date,
    co.OrderRN, a.AppleRN, p.PearRN
FROM 
    Orders AS o
OUTER JOIN 
    CTEOrder AS co ON o.OrderID = co.Orderid
OUTER JOIN 
    CTEApple AS a ON o.OrderID = a.OrderID
OUTER JOIN 
    CTEPear AS p ON o.OrderID = p.OrderID
WHERE 
    (co.OrderRN IS NULL AND a.AppleRN IS NULL AND p.PearRN IS NULL
     OR co.OrderRN = 1 AND a.AppleRN IS NULL AND p.PearRN IS NULL
     OR co.OrderRN = 1 AND a.AppleRN = 1 AND p.PearRN IS NULL
     OR co.OrderRN = 1 AND a.AppleRN = 1 AND p.PearRN = 1
     OR co.OrderRN = 1 AND a.AppleRN IS NULL AND p.PearRN = 1
     OR co.OrderRN IS NULL AND a.AppleRN = 1 AND p.PearRN IS NULL
     OR co.OrderRN IS NULL AND a.AppleRN = 1 AND p.PearRN = 1
     OR co.OrderRN IS NULL AND a.AppleRN IS NULL AND p.PearRN = 1)

Currently my result set is unwieldy with a significant amount of duplication.

I'm thinking that I am heading in the wrong direction, but I don't know what other tools are available to me within SQL Server to cut up this data the way I need.

Thanks for any guidance!

Here's my result set after Nik Shenoy's guidance:

+----------+-----------+----------------+
| OrderID  |    Date   | Apple? | Pear? |
+----------+-----------+----------------+
|    1     | 01/01/2001|    x   |  NULL |
|    1     | 01/01/2001|  NULL  |   x   | 
|    1     | 01/01/2001|  NULL  |   x   |
|    1     | 01/01/2001|  NULL  |  NULL |
|    1     | 01/01/2001|  NULL  |  NULL |
|    2     | 02/02/2002|  NULL  |  NULL |
|    2     | 02/02/2002|  NULL  |  NULL |
|    3     | 03/03/2003|  NULL  |  NULL |
|    3     | 03/03/2003|    x   |  NULL |
|    3     | 03/03/2003|  NULL  |  NULL |
+----------+-----------+----------------+

What is my next step to have only 1 row per Order:

+----------+-----------+--------+-------+
| OrderID  |   Date    | Apple? | Pear? |
+----------+-----------+--------+-------+
|    1     |01/01/2001 |    X   |   X   |
|    2     |02/02/2002 |        |       |
|    3     |03/03/2003 |    X   |       |
+----------+-----------+--------+-------+

Upvotes: 3

Views: 70

Answers (2)

Nik Shenoy
Nik Shenoy

Reputation: 701

If you know all the products in advance, you can use the Transact-SQL PIVOT relational operator to cross-tabulate the data by product. If you use MAX or COUNT, you can just transform non-NULL or non-ZERO output to an 'x'

SELECT
    PivotData.OrderID
,   PivotData.OrderDate
,   CASE WHEN PivotData.Apple IS NULL THEN '' ELSE 'X' END AS [Apple?]
,   CASE WHEN PivotData.Pear IS NULL THEN '' ELSE 'X' END AS [Pear?]
,   CASE WHEN PivotData.Orange IS NULL THEN '' ELSE 'X' END AS [Orange?]
,   CASE WHEN PivotData.Pineapple IS NULL THEN '' ELSE 'X' END AS     [Pineapple?]
,   CASE WHEN PivotData.Cherry IS NULL THEN '' ELSE 'X' END AS [Cherry?]
FROM
    (SELECT OrderID, Product, OrderDate) AS [Order]
PIVOT   (MAX(Product) FOR Product IN ( [Apple], [Pear], [Orange], [Pineapple], [Cherry] )) AS PivotData

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269913

You can just use conditional aggregation:

select o.orderid, date,
       max(case when product = 'Apple' then 'X' end) as IsApple,
       max(case when product = 'Pear' then 'X' end) as IsPear
from orders o
group by o.orderid, date;

Upvotes: 1

Related Questions