Generate Rows based on a Column Value

My requirement is the following:

In a database table the activity, start time and the number of repetitions is stored, but for the report this needs to be expanded. The number of repetitions dictates the number of rows per activity, each incremented by five minutes from the start time.

To get things going a simplified table is created, as well as some sample data.

create table activities
(activity      varchar2(10)
,activity_date date 
,no_of_reps    number
);

insert into activities values ('X', to_date ('20-03-2017 12:00', 'dd-mm-yyyy hh24:mi'), 2);
insert into activities values ('Y', to_date ('20-03-2017 12:30', 'dd-mm-yyyy hh24:mi'), 4);
insert into activities values ('Z', to_date ('20-03-2017 13:00', 'dd-mm-yyyy hh24:mi'), 3);

commit;

The sample data looks like the following output; Activity X was started at 12:00 o'clock and repeated twice; Activity Y was started at 12:30 and repeated four times; Activity Z was started at 13:00 and repeated three times.

select a.activity
      ,to_char (a.activity_date, 'dd-mm-yyyy hh24:mi') start_time
      ,a.no_of_reps
  from activities a
/


ACTIVITY   START_TIME       NO_OF_REPS
---------- ---------------- ----------
X          20-03-2017 12:00          2
Y          20-03-2017 12:30          4
Z          20-03-2017 13:00          3

New in Oracle 12c is the LATERAL join making it very easy to generate the required number of rows for each row in the table.

select a.activity
      ,to_char (a.activity_date, 'dd-mm-yyyy hh24:mi') start_time
  from activities a
      ,lateral (select 1 from dual connect by level <= a.no_of_reps)
/
ACTIVITY   START_TIME     
---------- ----------------
X          20-03-2017 12:00
X          20-03-2017 12:00
Y          20-03-2017 12:30
Y          20-03-2017 12:30
Y          20-03-2017 12:30
Y          20-03-2017 12:30
Z          20-03-2017 13:00
Z          20-03-2017 13:00
Z          20-03-2017 13:00

 9 rows selected 

In the example above the LATERAL join with the "Connect By Level" trick is used to generate rows.

For the last part of the requirement, increment the time with five minutes for each repetition, the Analytic Function ROW_NUMBER is used.
The ROW_NUMBER (minus one) is multiplied by a five minute interval which is added to the original start time of the activity.

select a.activity
      ,to_char (a.activity_date, 'dd-mm-yyyy hh24:mi') start_time
      ,to_char (a.activity_date 
                 + (row_number() over (partition by a.activity_date
                                                   ,a.activity
                                           order by null
                                      ) - 1)
                 * to_dsinterval ('0 00:05:00')
               ,'dd-mm-yyyy hh24:mi') as calculated_time
  from activities a
      ,lateral (select 1 from dual connect by level <= a.no_of_reps)
/

ACTIVITY   START_TIME       CALCULATED_TIME
---------- ---------------- ----------------
X          20-03-2017 12:00 20-03-2017 12:00
X          20-03-2017 12:00 20-03-2017 12:05
Y          20-03-2017 12:30 20-03-2017 12:30
Y          20-03-2017 12:30 20-03-2017 12:35
Y          20-03-2017 12:30 20-03-2017 12:40
Y          20-03-2017 12:30 20-03-2017 12:45
Z          20-03-2017 13:00 20-03-2017 13:00
Z          20-03-2017 13:00 20-03-2017 13:05
Z          20-03-2017 13:00 20-03-2017 13:10

 9 rows selected 

Links

• Using Lateral Inline Views (Oracle Docs)
• TO_DSINTERVAL (Oracle Docs)