CODEWITHSUNDEEP
sqloracle-databasedaterepeat
Diogo Santos
Diogo Santos

Reputation: 894

Oracle - get row repeated n times, with dynamic value on date

I'm sure there is an easy way to do this in SQL Oracle, but I'm not able to find it.

I have a table ( temp ) , with 3 columns (id_1, id_2, date ), where date is Date type. Each line is unique.

The output I want is to repeat each line 15 times, with a new date columns where I get in one row the original date, in the second the original + 1 day, in the third the original + 2 days, etc, for each original row...

Upvotes: 1

Views: 417

Answers (1)

Marmite Bomber
Marmite Bomber

Reputation: 21063

Define a helper CTO with 15 rows and simple cross joinit with your original table

with days as (
select rownum -1 as day_offset 
from dual connect by level <= 15)
select 
 a.id1, a.id2, a.date_d + b.day_offset new_date_d
from tab a
cross join days b
order by 1,2,3;

Example - for the sample data ...

select * from tab;

       ID1        ID2 DATE_D             
---------- ---------- -------------------
         1          1 01.01.2020 00:00:00
         2          2 01.01.2019 00:00:00

... you will get the following output

       ID1        ID2 NEW_DATE_D         
---------- ---------- -------------------
         1          1 01.01.2020 00:00:00
         1          1 02.01.2020 00:00:00
         1          1 03.01.2020 00:00:00
         1          1 04.01.2020 00:00:00
         1          1 05.01.2020 00:00:00
....
         2          2 13.01.2019 00:00:00
         2          2 14.01.2019 00:00:00
         2          2 15.01.2019 00:00:00

30 rows selected.

Alternativly you may use recursive subquery factoring

The *day offset is calculated recursively as days.day_offset + 1 is limited to 15 and used to build the new date value

with days( id1, id2, date_d, day_offset) as (
select id1, id2, date_d, 0 day_offset from tab
union all
select days.id1, days.id2, tab.date_d + days.day_offset + 1 as date_d,
       days.day_offset + 1 as day_offset
from tab
join days 
on tab.id1 = days.id1 and tab.id2 = days.id2
and days.day_offset +1 < 15)
select * from days
order by 1,2,3

Upvotes: 4

Related Questions