如何将同一表中的行拆分为列？

Question

假设我有一个非常简单的桌子，像这样：

​

​

我想要一个查询来返回1行的数据，如下所示：

​

​

我该怎么做呢？到目前为止，我尝试过的是：

SELECT
    od.OrderNo,
    CASE
        WHEN od.PartNo LIKE 'CAD%' THEN od.PartNo
        END AS [Part1],
    CASE
        WHEN od.PartNo LIKE 'CAD%' THEN od.DueDate
        END AS [DueDate1],
    CASE
        WHEN od.PartNo LIKE 'WISH%' THEN od.PartNo
        END AS [Part2],
    CASE
        WHEN od.PartNo LIKE 'WISH%' THEN od.DueDate
        END AS [DueDate2]
FROM OrderDet od
WHERE od.OrderNo = '20352E'
    AND (od.PartNo LIKE 'CAD%'
    OR od.PartNo LIKE 'WISH%')

我从中得到的结果如下：

​

​

我也尝试了一个自我连接，就像这样：

SELECT
    od.OrderNo,
    od.PartNo AS [Part1],
    od.DueDate AS [DueDate1],
    od2.PartNo AS [Part2],
    od2.DueDate AS [DueDate2]
FROM OrderDet od
    JOIN OrderDet od2 ON od.OrderNo = od2.OrderNo
WHERE od.OrderNo = '20352E'
    AND (od.PartNo LIKE 'CAD%'
    OR od2.PartNo LIKE 'WISH%')

但这也不起作用，但是，第8行是我想要的，只是不确定如何隔离，结果如下：

​

​

那么，是否有办法真正做到我想做的，并让它显示在一排？不能把我的头绕在这上面。任何帮助都将不胜感激，谢谢

Answer 1

使用group by子句并进行条件聚合

select OrderNo,
       max(case when PartNo LIKE 'CAD%' then 'CAD' end) as PartNo1,
       max(case when PartNo LIKE 'CAD%' then DueDate end) as DueDate1,
       max(case when PartNo LIKE 'WISH%' then 'WISH' end) as PartNo2,
       max(case when PartNo LIKE 'WISH%' then DueDate end) as DueDate2
from OrderDet o
where OrderNo = '20352E' and 
      (PartNo LIKE 'CAD%' or PartNo LIKE 'WISH%')
group by OrderNo; 

Answer 2

这个怎么样：

SELECT
    OrderNo,
    (SELECT  MAX(PartNo) FROM OrderDet) AS [Part1],
    (SELECT  MAX(DueDate) FROM OrderDet) AS [DueDate1],
    (SELECT  MIN(PartNo) FROM OrderDet) AS [Part2],
    (SELECT  MIN(DueDate) FROM OrderDet) AS [DueDate2]
FROM OrderDet
WHERE od.OrderNo = '20352E'
    AND (od.PartNo LIKE 'CAD%'
    OR od2.PartNo LIKE 'WISH%')
GROUP BY OrderNo