SQL Server Trying to Collapse Multiple Rows to Single Result

fykwrbwg 于 2023-06-21 发布在其他

关注(0)|答案(1)|浏览(153)

A rough example of my source data:

OrderStatus | CustomerID | OrderNbr | LoadNbr | Product | Quantity
------------+------------+----------+---------+---------+---------
   OPEN     |     1      | ORD00001 |    1    |  0012   | 12
   OPEN     |     1      | ORD00001 |    2    |  0024   | 20

And what I'm trying to achieve in a result:

OrderStatus | CustomerID | OrderNbr | Prod01 | Quantity01 | Prod02 | Quantity02 | Ratio01 | Ratio02
-------------+------------+----------+--------+------------+--------+------------+---------+---------
    OPEN     |     1      | ORD00001 |  0012  |     12     |  0024  |   20       |  37.5   |   62.5

For the sake of brevity I only included 2 products. But in the data I've been given there can be upwards of 8 products.

What I've been trying:

WITH OrderSummary AS (
  SELECT O.OrderStatus,
         O.CustomerID,
         O.OrderNbr,
         'TotalQty' = I1.Quantity + I2.Quantity,
         'Prod01' = I1.Product,
         'Quantity01' = I1.Quantity,
         'Prod02' = I2.Product,
         'Quantity02' = I2.Quantity
  FROM   Orders O
  LEFT JOIN Orders I1 ON O.OrderNbr = I1.OrderNbr AND I1.LoadNbr = 1
  LEFT JOIN Orders I2 ON O.OrderNbr = I2.OrderNbr AND I1.LoadNbr = 2
)

SELECT  *,
        'Ratio01' = Quantity01 / TotalQty * 100,
        'Ratio02' = Quantity02 / TotalQty * 100
FROM    OrderSummary

Which is more or less what I've currently got running. But the results I do get look like this:

OrderStatus | CustomerID | OrderNbr | Prod01 | Quantity01 | Prod02 | Quantity02 | Ratio01 | Ratio02
-------------+------------+----------+--------+------------+--------+------------+---------+---------
    OPEN     |     1      | ORD00001 |  0012  |     12     |  0024  |   20       |  37.5   |   62.5
    OPEN     |     1      | ORD00001 |  0012  |     12     |  0024  |   20       |  37.5   |   62.5

So I just tried running with a DISTINCT declaration which works most of the time...but there's order in my sample data that returns a duplicate row no matter what.

So the question I have is whether I'm approaching this problem from the right angle or not. Is using joins on the same table the best way to collapse the results to a single row? Or is there a better way to achieve this? I am somewhat constrained by the environment I'm working in: I have no control over the data format given to me and I must present the results in a single row to the application executing the database query.

EDIT: Some additional clarification. For any given order the Status, Customer, OrderNumber, etc. would all be the same. The LoadNbr, Product, and Quantity rows would be unique between each entry. So an Order with 8 products would have 8 rows I'm trying to collapse to a single row.

sql-server

来源：https://stackoverflow.com/questions/76427214/trying-to-collapse-multiple-rows-to-single-result

1条答案

按热度按时间

svdrlsy41#

In my view the simplest way to do this is through "conditional aggregates" and not via the "pivot operator". However first your existing query would work IF you include a where clause to suppress the unwanted rows: e.g:

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

but this is so very ugly and has problems (e.g. TotalQty could be NULL if any of the quantities are missing.

An alternative is to use an approach like this:

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

and this can be made "dynamic" as well, like so:

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;

see this demonstration

赞(0）回复(0）举报 2023-06-21

我来回答

SQL Server Trying to Collapse Multiple Rows to Single Result

1条答案

相关问题

热门标签

最新问答