Recursive CTE to split rows based on a column value-CodePudding

I have a CTE that returns a set of item IDs and quantities. I'm trying to use another CTE to split each row into multiple rows based on a defined container size. For example, if the container size is specified as 20 and the row quantity is 49, I'd like to split it into 2 rows of with quantity 20 and one row of 9.

Below is where I'm finally stuck. Is a recursive CTE the wrong choice for this scenario? Any help would be appreciated.

DECLARE @ContainerSize int = 20;

WITH ItemDetails (ItemID, Qty) AS (
    -- Query that returns data like below
    SELECT 29, 49
    UNION ALL
    SELECT 33, 64
    UNION ALL
    SELECT 38, 32
    UNION ALL
    SELECT 41, 54
),
ItemDetailsSplit(n, ItemID, Qty) AS (
    SELECT  
        0,
        ItemID,
        Qty
    FROM ItemDetails
    UNION ALL
    SELECT
        n   1,
        ItemID,
        CASE WHEN Qty < (@ContainerSize * (n   1))
            THEN Qty
            ELSE Qty - (@ContainerSize * (n   1))
        END AS [Qty]        
    FROM ItemDetailsSplit   
    WHERE ( Qty > (@ContainerSize * n) )
)
SELECT * 
FROM ItemDetailsSplit
ORDER BY ItemID, Qty DESC;

CodePudding user response：

Without knowing your specific RDBMS I have a solution that works with SQL Server, it's easily convertable to any database platform.

This uses a numbers table - here another CTE but in production you'd have a permanent table.

declare @ContainerSize int = 20;
with numbers (n) as (
    select top(100) Row_Number() over(order by(select null)) from master.dbo.spt_values
), ItemDetails (ItemID, Qty) as (
    -- Query that returns data like below
    select 29, 49
    union all
    select 33, 64
    union all
    select 38, 32
    union all
    select 41, 54
)
select ItemID, Iif(n <= Qty / @ContainerSize, @ContainerSize, Qty % @ContainerSize) Qty
from ItemDetails d
cross apply numbers n
where n <= (Qty / @ContainerSize)   Iif(Qty % @ContainerSize = 0, 0, 1)
order by ItemID, Qty

See working DB<>Fiddle