How to get all years between a range of begin and end date in sql oracle

I have a table evaluations like this

I want to convert the bgn_year and end_year into a eval_year by enumerate all years between the two ranges. the result would look like this:

I tried with a connect by:

select  employee,
     evaluator_type,
     EVALUATOR,
     (bgn_year-1)+level as eval_year
from evaluations
connect by  (bgn_year-1)+level <=end_year

But I have to many duplicated rows. I can add a distinct, but I don’t think that is the way to do it.

Answer

That would be something like this (sample data in lines #1 – 7; query you need begins at line #8):

SQL> with evaluations (employee, bgn_year, end_year, evaluator_type, evaluator)
  2  as
  3  (select 'A', 2019, 2021, 'POS1', 'X' from dual union all
  4   select 'A', 2018, 2021, 'POS2', 'Y' from dual union all
  5   select 'B', 2019, 2020, 'POS1', 'Z' from dual union all
  6   select 'B', 2020, 2021, 'POS1', 'X' from dual
  7  )
  8  select employee,
  9         --
 10         bgn_year + column_value - 1 as eval_year,
 11         --
 12         evaluator_type,
 13         evaluator
 14  from evaluations cross join
 15       table(cast(multiset(select level from dual
 16                           connect by level <= end_year - bgn_year + 1
 17                          ) as sys.odcinumberlist));

EMPLOYEE    EVAL_YEAR EVALUATOR_TYPE  EVALUATOR
---------- ---------- --------------- ----------
A                2019 POS1            X
A                2020 POS1            X
A                2021 POS1            X
A                2018 POS2            Y
A                2019 POS2            Y
A                2020 POS2            Y
A                2021 POS2            Y
B                2019 POS1            Z
B                2020 POS1            Z
B                2020 POS1            X
B                2021 POS1            X

11 rows selected.

SQL>