0

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? – Philᵀᴹ May 14 at 11:51
2
-- 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.