Getting the days between two dates for multiple IDs

Question

This is a follow up question from How to get a list of months between 2 given dates using a query? really. (I suspect it's because I don't quite understand the logic behind connect by level clauses !)

What I have is a list of data like so

ID | START_DATE  | END_DATE
 1 | 01-JAN-2018 | 20-JAN-2018
 2 | 13-FEB-2018 | 20-MAR-2018
 3 | 01-MAR-2018 | 07-MAR-2018

and what I want to try and get is a list with all the days between the start and end date for each ID.

So for example I want a list which gives

ID | DATE
 1 | 01-JAN-2018
 1 | 02-JAN-2018
 1 | 03-JAN-2018 
...
 1 | 19-JAN-2018
 1 | 20_JAN-2018
 2 | 13-FEB-2018
 2 | 14-FEB-2018
 2 | 15-FEB-2018 
...

etc.

What I've tried to do is adapt one of the answers from the above link as follows

select id
, trunc(start_date+((level-1)),'DD') 
from (
  select id
  , start_date
  , end_date
  from blah
 ) 
connect by level <= ((trunc(end_date,'DD')-trunc(start_date,'DD'))) + 1

which gives me what I want but then a whole host of duplicate dates as if it's like a cartesian join. Is there something simple I need to add to fix this?

Answer 1

I like recursive CTEs:

with cte as (
      select id, start_dte as dte, end_dte
      from blah
      union all
      select id, dte + 1, end_dte
      from cte
      where dte < end_dte
     )
select *
from cte
order by id, dte;

This is ANSI standard syntax and works in several other databases.