CODEWITHSUNDEEP
sqloracle-database
Ianthe
Ianthe

Reputation: 5709

Oracle : Generate rows with slightly different values in a column

I have a table with data like below :

ID       SUMMARY_DATE         KEYWORD_ID       DATA
123      9/1/2014              5                98

I need to generate 18 more rows with the summary_date + 18 months, something like below :

ID       SUMMARY_DATE         KEYWORD_ID       DATA
123      9/1/2014              5                98
123      10/1/2014             5                98
123      11/1/2014             5                98
123      12/1/2014             5                98
...
123      3/1/2016              5                98

I could do that using UNION but it will be so long. Is there any other ways to do it? Thanks in advance.

Upvotes: 0

Views: 44

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270583

Just generate a list of numbers and use add_months():

with n as (
      select level as m
      from dual
      connect by level <= 18
     )
select t.id, add_months(t.summary_date, n.m, keywork_id, data
from table t cross join
     n;

Upvotes: 2

Related Questions