input
NAME ID STR_DT END_DT
P 10 01-APR-17 04-APR-17
S 20 05-APR-17 07-APR-17
M 30 08-APR-17 10-APR-17
```
output
NAME ID DATA
P 10 01-APR-17
P 10 02-APR-17
P 10 03-APR-17
P 10 04-APR-17
S 20 05-APR-17
S 20 06-APR-17
S 20 07-APR-17
M 30 08-APR-17
M 30 09-APR-17
M 30 10-APR-17
Here you go:
SQL> alter session set nls_date_format = 'dd.mm.yyyy';
Session altered.
SQL> with test (name, id, str_dt, end_dt) as
2 (select 'P', 10, date '2017-04-01', date '2017-04-04' from dual union all
3 select 'S', 20, date '2017-04-05', date '2017-04-07' from dual union all
4 select 'M', 30, date '2017-04-08', date '2017-04-10' from dual
5 )
6 select name, id, (str_dt + column_value - 1) data
7 from test cross join table(cast(multiset(select level from dual
8 connect by level <= end_dt - str_dt + 1
9 ) as sys.odcinumberlist))
10 order by id, data;
N ID DATA
- ---------- ----------
P 10 01.04.2017
P 10 02.04.2017
P 10 03.04.2017
P 10 04.04.2017
S 20 05.04.2017
S 20 06.04.2017
S 20 07.04.2017
M 30 08.04.2017
M 30 09.04.2017
M 30 10.04.2017
10 rows selected.
SQL>