Generate a range of records depending on from-to dates

I have a table of records like this:

Item	From	To
A	2018-01-03	2018-03-16
B	2021-05-25	2021-11-10

The output of select should look like:

Item	Month	Year
A	01	2018
A	02	2018
A	03	2018
B	05	2021
B	06	2021
B	07	2021
B	08	2021

Also the range should not exceed the current month. In example above we are asuming current day is 2021-08-01.

I am trying to do something similar to THIS with CONNECT BY LEVEL but as soon as I also select my table next to dual and try to order the records the selection never completes. I also have to join few other tables to the selection but I don’t think that would make a difference.

I would very much appreciate your help.

Answer

Row generator it is, but not as you did it; most probably you’re missing lines #11 – 16 in my query (or their alternative).

SQL> with test (item, date_from, date_to) as
  2    -- sample data
  3    (select 'A', date '2018-01-03', date '2018-03-16' from dual union all
  4     select 'B', date '2021-05-25', date '2021-11-10' from dual
  5    )
  6  -- query that returns desired result
  7  select item,
  8         extract(month from (add_months(date_from, column_value - 1))) month,
  9         extract(year  from (add_months(date_from, column_value - 1))) year
 10  from test cross join
 11    table(cast(multiset
 12      (select level
 13       from dual
 14       connect by level <=
 15         months_between(trunc(least(sysdate, date_to), 'mm'), trunc(date_from, 'mm')) + 1
 16      ) as sys.odcinumberlist))
 17  order by item, year, month;

ITEM       MONTH       YEAR
----- ---------- ----------
A              1       2018
A              2       2018
A              3       2018
B              5       2021
B              6       2021
B              7       2021
B              8       2021

7 rows selected.

SQL>