Sql-server – spliting a row based on max values

sql serversql-server-2016

I have a table with 3 fields and one is a Qty. If the Qty is > than a set amount I would like to split the row into a second row and/or third row

Row   Order      Product     qty
1     123        AAA         25

Based on a max Qty of 10 I would like it to become

Row   Order      Product     qty
1     123        AAA         10
2     123        AAA         10
3     123        AAA         5

Best Answer

-- set chunk size
WITH cte0 AS ( SELECT 10 chunk 
             ),
-- calculate max chunks count
     cte1 AS ( SELECT 1 + MAX(qty / chunk) chunks
               FROM test, cte0 
             ),
-- generate numbers
     cte2 AS ( SELECT 1 num
               UNION ALL
               SELECT num+1
               FROM cte1, cte2
               WHERE num < chunks
             ),
-- prepare data
     cte3 AS ( SELECT num, Product, CASE WHEN qty > num * chunk 
                                         THEN chunk 
                                         ELSE qty - (num - 1) * chunk 
                                         END qty
               FROM test, cte0, cte2
               WHERE CASE WHEN qty > num * chunk 
                          THEN chunk 
                          ELSE qty - (num - 1) * chunk 
                          END > 0
             )
-- perform action
MERGE test
USING cte3
ON (test.Product = cte3.Product AND cte3.num =1)
WHEN MATCHED THEN 
    UPDATE SET Qty = cte3.qty
WHEN NOT MATCHED THEN
    INSERT (Product, Qty)
    VALUES (cte3.Product, cte3.qty);

fiddle

Related Question