CODEWITHSUNDEEP
teradata
madsthiru
madsthiru

Reputation: 51

Split a row into multiple rows - Teradata

Below is an example of my table

Names   Start_Date   Orders    Items
AAA     2020-01-01   300       100
BAA     2020-02-01   896       448

My requirement would be as below

Names   Start_Date   Orders   
AAA     2020-01-01   100   
AAA     2020-01-01   100
AAA     2020-01-01   100
BBB     2020-02-01   448
BBB     2020-02-01   448

The rows should be split based on the (Orders/Items) value

Upvotes: 0

Views: 1466

Answers (2)

Andrew
Andrew

Reputation: 8758

This calls for a recursive CTE. Here's how I'd approach it, with a lovely volatile table for some sample data.

    create volatile table vt_foo
    (names varchar(100), start_date date, orders int, items int)
    on commit preserve rows;

    insert into vt_foo values ('AAA','2020-01-01',300,100);
    insert into vt_foo values ('BAA','2020-02-01',896,448);
    insert into vt_foo values ('CCC','2020-03-01',525,100);  -


    with recursive cte (names, start_date,items, num, counter) as (
    select
    names,
    start_date,
    items,
round(orders /( items * 1.0) ) as num ,
    1 as counter
    from vt_foo
    UNION ALL
    select
    a.names,
    a.start_date,
    a.items,
    b.num,
    b.counter + 1
    from vt_foo a
    inner join cte b 
    on a.names = b.names
    and a.start_date =b.start_date
    where b.counter + 1 <= b.num
    )
    select  * from cte
    order by names,start_date

This bit: b.counter + 1 <= b.num is the key to limiting the output to the proper # of rows per product/date.

I think this should be ok, but test it with small volumes of data.

Upvotes: 1

dnoeth
dnoeth

Reputation: 60513

This is a nice task for Teradata's SQL extension to create time series (based on @Andrew's test data):

SELECT *
FROM vt_foo
EXPAND ON PERIOD(start_date, start_date + Cast(Ceiling(Cast(orders AS FLOAT)/items) AS INT)) AS pd

For an exact split of orders into items:

SELECT dt.*, 
   CASE WHEN items * (end_date - start_date) > orders 
        THEN orders MOD items
        ELSE items
   end
FROM
 (
   SELECT t.*, End(pd) AS end_date
   FROM vt_foo AS t
   EXPAND ON PERIOD(start_date, start_date + Cast(Ceiling(Cast(orders AS FLOAT)/items) AS INT)) AS pd 
 ) AS dt

Upvotes: 2

Related Questions