How to pivot column data into a row where a maximum qty total cannot be exceeded?

user_7068301 0
Viewed 68

Introduction:

I have come across an unexpected challenge. I'm hoping someone can help and I am interested in the best method to go about manipulating the data in accordance to this problem.

Scenario:

I need to combine column data associated to two different ID columns. Each row that I have associates an item_id and the quantity for this item_id. Please see below for an example.

+-------+-------+-------+---+
|cust_id|pack_id|item_id|qty|
+-------+-------+-------+---+
|     1 | A     |     1 | 1 |
|     1 | A     |     2 | 1 |
|     1 | A     |     3 | 4 |
|     1 | A     |     4 | 0 |
|     1 | A     |     5 | 0 |
+-------+-------+-------+---+

I need to manipulate the data shown above so that 24 rows (for 24 item_ids) is combined into a single row. In the example above I have chosen 5 items to make things easier. The selection format I wish to get, assuming 5 item_ids, can be seen below.

+---------+---------+---+---+---+---+---+
| cust_id | pack_id | 1 | 2 | 3 | 4 | 5 |
+---------+---------+---+---+---+---+---+
|       1 | A       | 1 | 1 | 4 | 0 | 0 |
+---------+---------+---+---+---+---+---+

However, here's the condition that is making this troublesome. The maximum total quantity for each row must not exceed 5. If the total quantity exceeds 5 a new row associated to the cust_id and pack_id must be created for the rest of the item_id quantities. Please see below for the desired output.

+---------+---------+---+---+---+---+---+
| cust_id | pack_id | 1 | 2 | 3 | 4 | 5 |
+---------+---------+---+---+---+---+---+
|       1 | A       | 1 | 1 | 3 | 0 | 0 |
|       1 | A       | 0 | 0 | 1 | 0 | 0 |
+---------+---------+---+---+---+---+---+

Notice how the quantities of item_ids 1, 2 and 3 summed together equal 6. This exceeds the maximum total quantity of 5 for each row. For the second row the difference is created. In this case only item_id 3 has a single quantity remaining.

Note, if a 2nd row needs to be created that total quantity displayed in that row also cannot exceed 5. There is a known item_id limit of 24. But, there is no known limit of the quantity associated for each item_id.

sql-server sql
1 Answers

Here's an approach which goes from left-field a bit.

One approach would have been to do a recursive CTE, building the rows one-by-one.

Instead, I've taken an approach where I

  • Create a new (virtual) table with 1 row per item (so if there are 6 items, there will be 6 rows)
  • Group those items into groups of 5 (I've called these rn_batches)
  • Pivot those (based on counts per item per rn_batch)

For these, processing is relatively simple

  • Creating one row per item is done using INNER JOIN to a numbers table with n <= the relevant quantity.
  • The grouping then just assigns rn_batch = 1 for the first 5 items, rn_batch = 2 for the next 5 items, etc - until there are no more items left for that order (based on cust_id/pack_id).

Here is the code

/*   Data setup   */

CREATE TABLE #Order (cust_id int, pack_id varchar(1), item_id int, qty int, PRIMARY KEY (cust_id, pack_id, item_id))
INSERT INTO #Order (cust_id, pack_id, item_id, qty) VALUES
(1, 'A', 1, 1),
(1, 'A', 2, 1),
(1, 'A', 3, 4),
(1, 'A', 4, 0),
(1, 'A', 5, 0);


/*  Pivot results  */

WITH Nums(n) AS
    (SELECT (c * 100) + (b * 10) + (a) + 1 AS n
        FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) A(a)
            CROSS JOIN (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) B(b)
            CROSS JOIN (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) C(c)
    ),
ItemBatches AS
(SELECT cust_id, pack_id, item_id, 
        FLOOR((ROW_NUMBER() OVER (PARTITION BY cust_id, pack_id ORDER BY item_id, N.n)-1) / 5) + 1 AS rn_batch
    FROM #Order O
        INNER JOIN Nums N ON N.n <= O.qty
)
SELECT * 
FROM (SELECT cust_id, pack_id, rn_batch, 'Item_' + LTRIM(STR(item_id)) AS item_desc 
     FROM ItemBatches
     ) src
PIVOT
    (COUNT(item_desc) FOR item_desc IN ([Item_1], [Item_2], [Item_3], [Item_4], [Item_5])) pvt
ORDER BY cust_id, pack_id, rn_batch;

And here are results

cust_id pack_id rn_batch    Item_1  Item_2  Item_3  Item_4  Item_5
1       A       1           1       1       3       0       0
1       A       2           0       0       1       0       0

Here's a db<>fiddle with

  • additional data in the #Orders table
  • the answer above, and also the processing with each step separated.

Notes

  • This approach (with the virtual numbers table) assumes a maximum of 1,000 for a given item in an order. If you need more, you can easily extend that numbers table by adding additional CROSS JOINs.
  • While I am in awe of the coders who made SQL Server and how it determines execution plans in millisends, for larger datasets I give SQL Server 0 chance to accurately predict how many rows will be in each step. As such, for performance, it may work better to split the code up into parts (including temp tables) similar to the db<>fiddle example.
user_142674250
Related