I have a table it likes below.
| id | start_range | end_range |
|---|---|---|
| pk1 | 202201 | 202204 |
| pk2 | 202202 | 202205 |
| pk3 | 202204 | 202209 |
What SQL in Oracle can help me get result likes below? Kindly help me out.
| id | perid |
|---|---|
| pk1 | 202201 |
| pk1 | 202202 |
| pk1 | 202203 |
| pk1 | 202204 |
| pk2 | 202202 |
| pk2 | 202203 |
| pk2 | 202204 |
| pk2 | 202205 |
| pk3 | 202204 |
| pk3 | 202205 |
| pk3 | 202206 |
| pk3 | 202207 |
| pk3 | 202208 |
| pk3 | 202209 |
The way you put it (i.e. with no explanation at all):
SQL> with test (id, start_range, end_range) as
2 (select 'pk1', 202201, 202204 from dual union all
3 select 'pk2', 202202, 202205 from dual union all
4 select 'pk3', 202204, 202209 from dual
5 )
6 select id,
7 start_range + column_value - 1 as perid
8 from test cross join
9 table(cast(multiset(select level from dual
10 connect by level <= end_range - start_range + 1
11 ) as sys.odcinumberlist))
12 order by id, perid;
ID PERID
--- ----------
pk1 202201
pk1 202202
pk1 202203
pk1 202204
pk2 202202
pk2 202203
pk2 202204
pk2 202205
pk3 202204
pk3 202205
pk3 202206
pk3 202207
pk3 202208
pk3 202209
14 rows selected.
SQL>
But, if I had to guess, I'd say that start and end range represent months. If that's really so, code I posted above wouldn't work properly, but the one that follows will (as you have to deal with dates in that case):
SQL> with test (id, start_range, end_range) as
2 (select 'pk1', 202201, 202204 from dual union all
3 select 'pk2', 202210, 202302 from dual
4 )
5 select id,
6 to_char(add_months(to_date(start_range, 'yyyymm'), column_value - 1), 'yyyymm') as perid
7 from test cross join
8 table(cast(multiset(select level from dual
9 connect by level <= months_between(to_date(end_range, 'yyyymm'),
10 to_date(start_range, 'yyyymm')) + 1
11 ) as sys.odcinumberlist))
12 order by id, perid;
ID PERID
--- ------
pk1 202201
pk1 202202
pk1 202203
pk1 202204
pk2 202210
pk2 202211
pk2 202212
pk2 202301
pk2 202302
9 rows selected.
SQL>