Recursive CTE to split rows based on a column value

Question

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;

You probably don't need a recursive CTE here, tag your specific database eg, MySql, Postgres, SQL Server etc... — Stu
– Stu, Commented Nov 25, 2021 at 21:35

Stu · Accepted Answer · 2021-11-25 21:48:10Z

2

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

answered Nov 25, 2021 at 21:48

Stu

32.7k6 gold badges17 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

scumdogg Over a year ago

That worked for me. Thanks! Only change I had to make was casting for the modulo operator, CAST(CAST(Qty AS decimal(38,19)) % @ContainerSize AS float), since my true values are floats and not ints like in my example. Not pretty, but works for now.

scumdogg Over a year ago

One other change I'd share, since the master.dbo.spt_values table is an undocumented table I can't trust that it will always be there so changed the numbers query to: WITH numbers (n) AS ( SELECT 1 AS n UNION ALL SELECT n + 1 FROM numbers WHERE n + 1 <= 100 ),

Stu Over a year ago

@scumdogg Yes this was purely for a quick example, a recursive CTE also works of course and is fine for smallish data sets, sometimes though a permanent numbers table makes sense for ranges of 1000s or millions of rows and if oyu have a permanent numbers table it just makes sense to use it.

Collectives™ on Stack Overflow

Recursive CTE to split rows based on a column value

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related