# spliting a row based on max values

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
``````
• In pure SQL or T-SQL? May 14, 2019 at 11:51

``````-- 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