SQL Server - Sum based on another column, and combine with STUFF/XML PATH?

Question

I'm not sure if this is incredibly convoluted, so apologies in advance.

CREATE TABLE TestOrder
(
[OrderLineID] INT NOT NULL IDENTITY PRIMARY KEY,
[Item] NVARCHAR(30) NULL,
[OrderQty] INT NULL,
[Status1] INT NULL, 
[Quantity_at_sts1] INT NULL,
[Status2] INT NULL, 
[Quantity_at_sts2] INT NULL,
[Status3] INT NULL, 
[Quantity_at_sts3] INT NULL,
[Status4] INT NULL, 
[Quantity_at_sts4] INT NULL,
[Status5] INT NULL, 
[Quantity_at_sts5] INT NULL,
[OrderRef] NVARCHAR(10) NULL
)

INSERT INTO TestOrder
values
('TSHIRT','1','100','1','0','0','0','0','0','0','0','0','Ord.1'),
('SOCKS','4','50','4','0','0','0','0','0','0','0','0','Ord.2'),
('SHIRT','5','10','1','50','2','0','0','0','0','0','0','Ord.3'),
('SHIRT','5','100','2','0','0','0','0','0','0','0','0','Ord.3'),
('SOCKS','10','10','4','50','2','0','0','0','0','0','0','Ord.4'),
('SOCKS','10','50','3','100','1','0','0','0','0','0','0','Ord.4')

.

| OrderLineID | Item | OrderQty | Status1 | Quantity_at_sts1 | Status2 | Quantity_at_sts2 | Status3 | Quantity_at_sts3 | Status4 | Quantity_at_sts_4 | Status5 | Quantity_at_sts5 | OrderRef |
|-------------|------|----------|---------|------------------|---------|------------------|---------|------------------|---------|-------------------|---------|------------------|----------|
|    1        |TSHIRT|   1      |    100  |       1          |   0     |    0             |   0     |         0        |    0    |         0         |    0    |         0        |   Ord.1  |
|    2        |SOCKS |   4      |    50   |       4          |   0     |    0             |   0     |         0        |    0    |         0         |    0    |         0        |   Ord.2  |
|    3        |SHIRT |   5      |    10   |       1          |   50    |    2             |   0     |         0        |    0    |         0         |    0    |         0        |   Ord.3  |
|    4        |SHIRT |   5      |    100  |       2          |   0     |    0             |   0     |         0        |    0    |         0         |    0    |         0        |   Ord.3  |
|    5        |SOCKS |   10     |    10   |       4          |   50    |    2             |   0     |         0        |    0    |         0         |    0    |         0        |   Ord.4  |
|    6        |SOCKS |   10     |    50   |       3          |   100   |    1             |   0     |         0        |    0    |         0         |    0    |         0        |   Ord.4  |

So this table is effectively an order table, and all of the "status1, quantity_at_sts1, status2..." etc. columns are effectively the progress of that order. Imagine you have various statuses between 10 to 100, 10 being 'not started', 100 being 'complete and despatched'. The "OrderRef" column is actually the key identifier for these orders of interest; e.g. the 4 rows for 'Ord.3' and 'Ord.4' are actually just one single item on each order, but with different statuses on each row.

Up till now, I've been using a case statement to get all of these statuses into one easier-to-read column (ignore all the casts as they are only required on the actual data):

select
CASE
WHEN status2 = 0 THEN '['+CAST(status1 AS NVARCHAR(10))+'] '+CAST(CAST(CAST(quantity_at_sts1 AS DECIMAL(20, 6)) AS FLOAT) AS NVARCHAR(10))
WHEN status2 > 0 AND status3 = 0 THEN '['+CAST(status1 AS NVARCHAR(10))+'] '+CAST(CAST(CAST(quantity_at_sts1 AS DECIMAL(20, 6)) AS FLOAT) AS NVARCHAR(10))+' - '+'['+CAST(status2 AS NVARCHAR(10))+'] '+CAST(CAST(CAST(quantity_at_sts2 AS DECIMAL(20, 6)) AS FLOAT) AS NVARCHAR(10))
WHEN status3 > 0 AND status4 = 0 THEN '['+CAST(status1 AS NVARCHAR(10))+'] '+CAST(CAST(CAST(quantity_at_sts1 AS DECIMAL(20, 6)) AS FLOAT) AS NVARCHAR(10))+' - '+'['+CAST(status2 AS NVARCHAR(10))+'] '+CAST(CAST(CAST(quantity_at_sts2 AS DECIMAL(20, 6)) AS FLOAT) AS NVARCHAR(10))+' - ['+CAST(status3 AS NVARCHAR(10))+'] '+CAST(CAST(CAST(quantity_at_sts3 AS DECIMAL(20, 6)) AS FLOAT) AS NVARCHAR(10))
WHEN status4 > 0 AND status5 = 0 THEN '['+CAST(status1 AS NVARCHAR(10))+'] '+CAST(CAST(CAST(quantity_at_sts1 AS DECIMAL(20, 6)) AS FLOAT) AS NVARCHAR(10))+' - '+'['+CAST(status2 AS NVARCHAR(10))+'] '+CAST(CAST(CAST(quantity_at_sts2 AS DECIMAL(20, 6)) AS FLOAT) AS NVARCHAR(10))+' - ['+CAST(status3 AS NVARCHAR(10))+'] '+CAST(CAST(CAST(quantity_at_sts3 AS DECIMAL(20, 6)) AS FLOAT) AS NVARCHAR(10)) + ' - ['+CAST(status4 AS NVARCHAR(10))+'] '+CAST(CAST(CAST(quantity_at_sts4 AS DECIMAL(20, 6)) AS FLOAT) AS NVARCHAR(10))
when status5 > 0 then '['+CAST(status1 AS NVARCHAR(10))+'] '+CAST(CAST(CAST(quantity_at_sts1 AS DECIMAL(20, 6)) AS FLOAT) AS NVARCHAR(10))+' - '+'['+CAST(status2 AS NVARCHAR(10))+'] '+CAST(CAST(CAST(quantity_at_sts2 AS DECIMAL(20, 6)) AS FLOAT) AS NVARCHAR(10))+' - ['+CAST(status3 AS NVARCHAR(10))+'] '+CAST(CAST(CAST(quantity_at_sts3 AS DECIMAL(20, 6)) AS FLOAT) AS NVARCHAR(10)) + ' - ['+CAST(status4 AS NVARCHAR(10))+'] '+CAST(CAST(CAST(quantity_at_sts4 AS DECIMAL(20, 6)) AS FLOAT) AS NVARCHAR(10)) + ' - ['+CAST(status5 AS NVARCHAR(10))+'] '+CAST(CAST(CAST(quantity_at_sts5 AS DECIMAL(20, 6)) AS FLOAT) AS NVARCHAR(10))
END AS 'all_statuses'
, *
from testorder

.

| all_statuses     |  OrderLineID | Item | OrderQty | Status1 | Quantity_at_sts1 | Status2 | Quantity_at_sts2 | Status3 | Quantity_at_sts3 | Status4 | Quantity_at_sts_4 | Status5 | Quantity_at_sts5 | OrderRef |
|------------------|--------------|------|----------|---------|------------------|---------|------------------|---------|------------------|---------|-------------------|---------|------------------|----------|
| [100] 1          |      1       |TSHIRT|   1      |    100  |       1          |   0     |    0             |   0     |         0        |    0    |         0         |    0    |         0        |   Ord.1  |
| [50] 4           |      2       |SOCKS |   4      |    50   |       4          |   0     |    0             |   0     |         0        |    0    |         0         |    0    |         0        |   Ord.2  |
| [10] 1 - [50] 2  |      3       |SHIRT |   5      |    10   |       1          |   50    |    2             |   0     |         0        |    0    |         0         |    0    |         0        |   Ord.3  |
| [100] 2          |      4       |SHIRT |   5      |    100  |       2          |   0     |    0             |   0     |         0        |    0    |         0         |    0    |         0        |   Ord.3  |
| [10] 4 - [50] 2  |      5       |SOCKS |   10     |    10   |       4          |   50    |    2             |   0     |         0        |    0    |         0         |    0    |         0        |   Ord.4  |
| [50] 3 - [100] 1 |      6       |SOCKS |   10     |    50   |       3          |   100   |    1             |   0     |         0        |    0    |         0         |    0    |         0        |   Ord.4  |

My ideal is to roll up by each unique OrderRef value, with the all_statuses being sum quantities across ALL rows with that OrderRef value:

| all_statuses               |  OrderQty  |  OrderRef |
|----------------------------|------------|-----------|
| [100] 1                    |      1     |   Ord.1   |
| [50] 4                     |      4     |   Ord.2   |
| [10] 1 - [50] 2 - [100] 2  |      5     |   Ord.3   |
| [10] 4 - [50] 5 - [100] 1  |      10    |   Ord.4   |

, however I have 2 issues I am struggling with:

1. Getting all of the statuses (even across multiple rows) into a single column
2. Summing up the quantities across multiple rows that have the SAME status (e.g. in Ord.4's case)

I have a little experience with STUFF FOR XML PATH and thought I might be able to do some kind of ridiculous CASE with STUFF combination, but I'm struggling to find a method. Is this a little too complicated to do?

Answer 1

The first step is to unpivot your columns to rows, which you can do using CROSS APPLY and a table value constructor

SELECT  o.OrderRef, x.*
FROM    #TestOrder AS o
        CROSS APPLY
        (VALUES 
            (1, o.Status1, o.Quantity_at_sts1),
            (2, o.Status2, o.Quantity_at_sts2),
            (3, o.Status3, o.Quantity_at_sts3),
            (4, o.Status4, o.Quantity_at_sts4),
            (5, o.Status5, o.Quantity_at_sts5)
        ) x (StatusNumber, StatusID, Quantity)
WHERE   x.StatusID > 0;

Which gives:

OrderRef    StatusNumber    StatusID    Quantity
--------------------------------------------------
Ord.1           1              100          1
Ord.2           1              50           4
Ord.3           1              10           1
Ord.3           2              50           2
Ord.3           1              100          2
Ord.4           1              10           4
Ord.4           2              50           2
Ord.4           1              50           3
Ord.4           2              100          1

Then you need to sum up the quantity, grouped by order, which is a simple SUM/GROUP BY. I have also added a ROW_NUMBER() column to use later when ordering your items during concatenation:

SELECT  o.OrderRef,
        Quantity = SUM(x.Quantity),
        x.StatusID,
        RowNumber = ROW_NUMBER() OVER(PARTITION BY o.OrderRef ORDER BY MIN(o.OrderLineID), MIN(x.StatusNumber))
FROM    #TestOrder AS o
        CROSS APPLY
        (VALUES 
            (1, o.Status1, o.Quantity_at_sts1),
            (2, o.Status2, o.Quantity_at_sts2),
            (3, o.Status3, o.Quantity_at_sts3),
            (4, o.Status4, o.Quantity_at_sts4),
            (5, o.Status5, o.Quantity_at_sts5)
        ) x (StatusNumber, StatusID, Quantity)
WHERE   x.StatusID > 0
GROUP BY x.StatusID, o.OrderRef

OrderRef    Quantity    StatusID    RowNumber
----------------------------------------------
Ord.1           1          100          1
Ord.2           4          50           1
Ord.3           1          10           1
Ord.3           2          50           2
Ord.3           2          100          3
Ord.4           4          10           1
Ord.4           5          50           2   <-- TWO ROWS GROUPED HERE
Ord.4           1          100          3

Finally, you can use the above derived table to create your strings, and then concatenate them using SQL Server's XML extensions:

WITH ALLStatuses AS
(   SELECT  o.OrderRef,
            Quantity = SUM(x.Quantity),
            x.StatusID,
            RowNumber = ROW_NUMBER() OVER(PARTITION BY o.OrderRef ORDER BY MIN(o.OrderLineID), MIN(x.StatusNumber))
    FROM    #TestOrder AS o
            CROSS APPLY
            (VALUES 
                (1, o.Status1, o.Quantity_at_sts1),
                (2, o.Status2, o.Quantity_at_sts2),
                (3, o.Status3, o.Quantity_at_sts3),
                (4, o.Status4, o.Quantity_at_sts4),
                (5, o.Status5, o.Quantity_at_sts5)
            ) x (StatusNumber, StatusID, Quantity)
    WHERE   x.StatusID > 0
    GROUP BY x.StatusID, o.OrderRef
) 
SELECT  all_statuses  = STUFF(( SELECT  CONCAT(' - ', QUOTENAME(S.StatusID), ' ', s.Quantity)
                                FROM    ALLStatuses AS s
                                WHERE   s.OrderRef = o.OrderRef
                                ORDER BY RowNumber
                                FOR XML PATH(''), TYPE
                            ).value('.', 'NVARCHAR(MAX)'), 1, 3, ''),
        o.OrderQty,
        o.OrderRef
FROM    #TestOrder AS o
GROUP BY o.OrderRef, o.OrderQty;

Output

all_statuses                OrderQty      OrderRef
------------------------------------------------
[100] 1                         1           Ord.1
[50] 4                          4           Ord.2
[10] 1 - [50] 2 - [100] 2       5           Ord.3
[10] 4 - [50] 5 - [100] 1       10          Ord.4