CODEWITHSUNDEEP
amazon-web-servicesamazon-redshift
luc
luc

Reputation: 83

Redshift generate rows as many as value in another column

df

customer_code contract_code    product  num_products
    C0134        AB01245        toy_1        4 
    B8328        EF28421        doll_4       2

I would like to transform this table based on the integer value in column num_products and generate a unique id for each row:

Expected_df

unique_id  customer_code contract_code     product      num_products
      A1           C0134        AB01245        toy_1        1 
      A2           C0134        AB01245        toy_1        1
      A3           C0134        AB01245        toy_1        1
      A4           C0134        AB01245        toy_1        1
      A5           B8328        EF28421        doll_4       1
      A6           B8328        EF28421        doll_4       1

Upvotes: 0

Views: 913

Answers (1)

Bill Weiner
Bill Weiner

Reputation: 11032

You need to use a recursive CTE to generate the series of number. Then join this with you data to produce the extra rows. I used row_number() to get the unique_id in the example below.

This should meet you needs or at least give you a start:

create table df (customer_code varchar(16),
                 contract_code varchar(16),
                 product varchar(16),
                 num_products int);

insert into df values
('C0134', 'AB01245', 'toy_1', 4),
('B8328', 'EF28421', 'doll_4', 2);

with recursive nums (n) as 
( select 1 as n
  union all
  select n+1 as n
  from nums 
  where n < (select max(num_products) from df) )
select row_number() over() as unique_id, customer_code, contract_code, product, num_products 
from df d
left join nums n
on d.num_products >= n.n;

SQLfiddle at http://sqlfiddle.com/#!15/d829b/12

Upvotes: 3

Related Questions