CODEWITHSUNDEEP
sqlteradata
ADJ
ADJ

Reputation: 102

Deaggregate into single unit level

I have the following table that I need to break apart or deaggregate:

Order Table: 

Order Id Item   Qty
O1       A1      5
O2       A2      1
O3       A3      3

Please provide SQL which will explode the above data into single unit level records as shown below:

Desired Output: 

Order Id    Item    Qty
O1          A1       1
O1          A1       1
O1          A1       1
O1          A1       1
O1          A1       1
O2          A2       1
O3          A3       1
O3          A3       1
O3          A3       1

How could I do this in Teradata?

Upvotes: 0

Views: 3065

Answers (4)

kritika
kritika

Reputation: 21

with main1 as(
select orderid,item, qty, rownum rn from orders)
select orderid,item, '1' from (select * from 
main1
where rn = 1) 
connect by level <= qty
union all
select orderid,item,'1' from (select * from 
main1
where rn = 2) 
connect by level <= qty
union all
select orderid,item,'1' from (select * from 
main1
where rn = 3) 
connect by level <= qty
;

Upvotes: 0

mgm_data
mgm_data

Reputation: 57

Another solution using replicate and string_split

select order_id, item, value
from (
select order_id, item, 
iif(quantity = 1, '1', concat('1',replicate(',1',(quantity-1)))) as split_quant
from orders) a
  CROSS APPLY STRING_SPLIT(split_quant,',') ;

for more info on string_split : https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-2017

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

An approach with a recursive cte.

with recursive cte(orderid,item,qty,val) as 
        (select orderid, item, qty, 1
         from t --this is your tablename
         union all
         select c.orderid, c.item, c.qty-1, c.val
         from t
         join cte c on t.orderid=c.orderid and t.item=c.item and c.qty>1
        )
select orderid,item,val as qty
from cte 
order by 1,2

Or with a numbers table (Generate all numbers = max quantity you can have) 

with recursive cte(num) as (select 1 num
                            union all
                            select num+1 from cte where num < 1000 --change this to max quantity
                            )
select orderid, item, 1 as qty
from t
join cte c on t.qty >= c.num
order by 1,2

Upvotes: 0

dnoeth
dnoeth

Reputation: 60482

You can either JOIN to a table with numbers from 1 to n using on n between 1 and Qty (resulting in a Product Join) or utilize EXPAND ON:

select OrderId, Item, 1
from tab
expand on period (current_date, current_date + Qty) as pd

Upvotes: 3

Related Questions