PLSQL How to split each row based on begin and end date-CodePudding

Could you please assist me to split each row and create multiple rows for each date between begin_date and end_date.

ID      CODE    VIEW  BEGIN_DATE   END_DATE
-------------------------------------------
10400   null    2     17-FEB-20    17-FEB-20
10650   null    2     17-FEB-20    18-FEB-20
10900   null    2     19-FEB-20    21-FEB-20
10901   null    2     21-FEB-20    02-MAR-20
11650   2723    2     02-MAR-20    04-MAR-20
11650   1002    2     02-MAR-20    04-MAR-20
11650   1001    2     02-MAR-20    04-MAR-20
11650   1000    2     02-MAR-20    04-MAR-20

Currently I'm using below query but it doesn't seem to work

select 
r.*
from rec r
connect by level <=  end_date - begin_date   1;

what i want is to some thing like this

ID      CODE    VIEW  DATE 
----------------------------------
11650   2723    2     02-MAR-20    
11650   2723    2     03-MAR-20    
11650   2723    2     04-MAR-20   
.... continue

CodePudding user response：

Here's one option:

Sample data:

SQL> with test (id, code, cview, begin_date, end_date) as
  2    (select 10400, null, 2, date '2020-02-17', date '2020-02-17' from dual union all
  3     select 10650, null, 2, date '2020-02-17', date '2020-02-18' from dual union all
  4     select 11650, 2723, 2, date '2020-03-02', date '2020-03-04' from dual
  5    )

Query begins here:

  6  select id,
  7         code,
  8         cview,
  9         begin_date   column_value - 1 as datum
 10  from test cross join
 11    table(cast(multiset(select level from dual
 12                        connect by level <= end_date - begin_date   1
 13                       ) as sys.odcinumberlist))
 14  order by id, datum;

        ID       CODE      CVIEW DATUM
---------- ---------- ---------- ----------
     10400                     2 17.02.2020
     10650                     2 17.02.2020
     10650                     2 18.02.2020
     11650       2723          2 02.03.2020
     11650       2723          2 03.03.2020
     11650       2723          2 04.03.2020

6 rows selected.

SQL>