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:
x
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.
Advertisement
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>