Sql try 将多行折叠为单个结果

在我看来，要做到这一点，最简单的方法是通过"条件聚合"，而不是通过"轴运算符".但是，首先，如果您包括一个WHERE子句来隐藏不需要的行，那么现有的查询将会起作用:例如:

WITH OrderSummary
AS (
    SELECT
          O.OrderStatus
        , O.CustomerID
        , O.OrderNbr
        , 'TotalQty' = O.Quantity + I2.Quantity + I3.Quantity + I4.Quantity 
                    + I5.Quantity + I6.Quantity + I7.Quantity + I8.Quantity
        ,     'Prod01' = O.Product
        , 'Quantity01' = O.Quantity
        ,     'Prod02' = I2.Product
        , 'Quantity02' = I2.Quantity
        ,     'Prod03' = I3.Product
        , 'Quantity03' = I3.Quantity
        -- more of the same here
    FROM Orders O
    LEFT JOIN Orders I2 ON O.OrderNbr = I2.OrderNbr AND I2.LoadNbr = 2
    LEFT JOIN Orders I3 ON O.OrderNbr = I3.OrderNbr AND I3.LoadNbr = 3
    LEFT JOIN Orders I4 ON O.OrderNbr = I4.OrderNbr AND I4.LoadNbr = 4
    -- more of the same here
    WHERE O.LoadNbr = 1
    )
SELECT
      *
    , 'Ratio01' = Quantity01 / TotalQty * 100
    , 'Ratio02' = Quantity02 / TotalQty * 100
    , 'Ratio03' = Quantity03 / TotalQty * 100
    -- more of the same here
FROM OrderSummary

但这非常难看，而且有问题(例如，如果缺少任何数量，TotalQty可能为空.

另一种方法是使用如下方法:

SELECT
      OrderStatus     
    , CustomerID     
    , OrderNbr     
    , ca.TotalQty     
    , max(CASE WHEN o.LoadNbr = 1 THEN o.Product ELSE '' END) AS Product1
    , max(CASE WHEN o.LoadNbr = 1 THEN o.Quantity ELSE 0 END) AS Quantity1
    , max(CASE WHEN o.LoadNbr = 1 THEN round(o.Quantity * 100.0 / ca.TotalQty,2) ELSE 0 END) AS Ratio1
    , max(CASE WHEN o.LoadNbr = 2 THEN o.Product ELSE '' END) AS Product2
    , max(CASE WHEN o.LoadNbr = 2 THEN o.Quantity ELSE 0 END) AS Quantity2
    , max(CASE WHEN o.LoadNbr = 2 THEN round(o.Quantity * 100.0 / ca.TotalQty,2) ELSE 0 END) AS Ratio2
    -- more of the same here
FROM Orders AS O
CROSS APPLY (
    SELECT SUM(q.Quantity) AS TotalQty
    FROM Orders AS q
    WHERE q.OrderNbr = o.OrderNbr     
    ) AS ca
GROUP BY
      OrderStatus     
    , CustomerID     
    , OrderNbr  

这也可以设置为"动态"，如下所示:

DECLARE @cols AS NVARCHAR(MAX);
DECLARE @sql AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT ', max(CASE WHEN o.LoadNbr = ' 
  + CONVERT(VARCHAR(10), LoadNbr) + ' THEN o.Product ELSE '''' END) AS Product' 
  + CONVERT(VARCHAR(10), LoadNbr) + ', max(CASE WHEN o.LoadNbr = ' 
  + CONVERT(VARCHAR(10), LoadNbr) + ' THEN o.Quantity ELSE 0 END) AS Quantity' 
  + CONVERT(VARCHAR(10), LoadNbr) + ', max(CASE WHEN o.LoadNbr = ' 
  + CONVERT(VARCHAR(10), LoadNbr) + ' THEN round(o.Quantity * 100.0 / ca.TotalQty,2) ELSE 0 END) AS Ratio' 
  + CONVERT(VARCHAR(10), LoadNbr)
             FROM (SELECT DISTINCT LoadNbr FROM Orders) O
             FOR XML PATH(''), TYPE
             ).value('.', 'NVARCHAR(MAX)') 
         ,1,2,'');

select @cols;

SET @sql = 'SELECT OrderStatus
    , CustomerID
    , OrderNbr
    , ca.TotalQty
    , ' + @cols + '
FROM Orders AS O
CROSS APPLY (
    SELECT SUM(q.Quantity) AS TotalQty FROM Orders AS q
    WHERE q.OrderNbr = o.OrderNbr
    ) AS ca
GROUP BY OrderStatus
    , CustomerID
    , OrderNbr
    , ca.TotalQty';

select @sql;

EXEC sp_executesql @sql;

见this demonstration