SQL Server : display distinct list of orders, 并指示哪些订单包含特定产品

Question

我有一个包含大量数据的表，但表中的相关数据如下所示:

订单表:

+----------+-----------+---------------+
| 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   |
+----------+-----------+---------------+

我希望查询返回不同的订单列表，并且如果订单包含某些产品，则进行如下指示:

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

这是我停下来并决定寻求帮助的地方:

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)

目前我的结果集很笨重，有大量重复。

我认为我正朝着错误的方向前进，但我不知道 SQL Server 中还可以使用哪些其他工具来按照我需要的方式分割这些数据。

感谢您的指导!

这是我在 Nik Shenoy 的指导下得到的结果:

+----------+-----------+----------------+
| 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 |
+----------+-----------+----------------+

我的下一步是什么，每个订单只有 1 行:

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

Answer 1

您可以只使用条件聚合:

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;