1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33
| WITH t1 AS
(
SELECT 1 as id, 2 as val, 1 as deb, 4 as fin FROM dual union ALL
SELECT 1 , 4 , 5 , 6 FROM dual union ALL
SELECT 2 , 3 , 1 , 2 FROM dual
)
, t2 as
(
select max(fin) - min(deb) + 1 as cpt_inter,
min(deb) as cpt_min
from t1
)
, t3 as
(
select level - 1 + cpt_min as cpt
from t2
CONNECT by level <= cpt_inter
)
select t1.id, t1.val, t3.cpt
from t1
inner join t3
on t3.cpt between t1.deb and t1.fin
order by t1.id asc, t1.val asc;
ID VAL CPT
1 2 1
1 2 2
1 2 3
1 2 4
1 4 5
1 4 6
2 3 1
2 3 2 |
Partager