How to create multiple rows from a single row in Redshift SQL

Question

I want to expand a single row to multiple rows in my table based on a column in the table in AWS Redshift.

Here is my example table schema and rows:

CREATE TABLE test (
  start timestamp, -- start time of the first slot
  slot_length int, -- the length of the slots in minutes
  repeat int       -- how many slots will be there
);

INSERT INTO test (start, slot_length, repeat) VALUES
('2019-09-22T00:00:00', 90, 2),
('2019-09-21T15:30:00', 60, 3);

I want to expand these two rows into 5 based on the value of the "repeat" column. So any row will be expanded "repeat" times. The first expansion won't change anything. The subsequent expansions need to add "slot_length" to the "start" column. Here is the final list of rows I want to have in the end:

'2019-09-22 00:00:00', 90, 2  -- expanded from the first row 
'2019-09-22 01:30:00', 90, 2  -- expanded from the first row
'2019-09-21 15:30:00', 60, 3  -- expanded from the second row
'2019-09-21 16:30:00', 60, 3  -- expanded from the second row
'2019-09-21 17:30:00', 60, 3  -- expanded from the second row

Can this be done via pure SQL in Redshift?

Might be similar: [Convert one row into multiple rows with fewer columns](https://stackoverflow.com/q/45201170/174777) Your requirements to have variable slot durations make the task more difficult to do in pure SQL. Might be a good use-case for a [Stored Procedure](https://docs.aws.amazon.com/redshift/latest/dg/stored-procedure-create.html), or simply do the work outside of Redshift an import the results. — John Rotenstein, Feb 15 '20 at 02:07

SwapSays · Answer 1 · 2020-02-18T06:59:37.577

This SQL should solves your purpose. Kindly Up-vote if it does.

    select t.start
    , case when rpt.repeat>1 
           then dateadd(min,t.slot_length*(rpt.repeat-1),t.start) 
           else t.start 
           end as new_start
    , t.slot_length
    , t.repeat
    from schema.test t
    join (select row_number() over() as repeat from schema.random_table) rpt
    on t.repeat>=rpt.repeat
    order by t.slot_length desc,rpt.repeat;

Please note that the "random_table" in your schema should have at least as many rows as the maximum value in your "repeat" column.

How to create multiple rows from a single row in Redshift SQL

1 Answers1