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
| With awy_sect as
(
select 'AWY1' as airway_name, 1 as order_num, 'PT1' as point_name from dual union all
select 'AWY1' , 2 , 'PT2' from dual union all
select 'AWY1' , 3 , 'PT3' from dual union all
select 'AWY2' , 1 , 'PTA' from dual union all
select 'AWY2' , 2 , 'PTB' from dual union all
select 'AWY2' , 3 , 'PTC' from dual union all
select 'AWY2' , 4 , 'PTD' from dual
)
, sr as
(
select airway_name, point_name as point_courant,
lead(point_name) over(partition by airway_name order by order_num asc) as point_suivant
from awy_sect
)
select airway_name, point_courant, point_suivant
from sr
where point_suivant is not null;
AIRWAY_NAME POINT_COURANT POINT_SUIVANT
----------- ------------- -------------
AWY1 PT1 PT2
AWY1 PT2 PT3
AWY2 PTA PTB
AWY2 PTB PTC
AWY2 PTC PTD |
Partager