I currently have a table that has a list of locations a schedule will use and I want to be able to get the location code and platform for both the current row and the next one, and then have the database return a distinct pair.
Sometimes the Platform is null.
Table:
HeaderId LocationOrdinal TIPLOC Platform
1 0 GRAINEW 1
1 1 GRAIN A
1 2 CLFFD12
1 3 HOOJ 2
1 4 GRVSEND 5
1 5 SPHEADJ A
1 6 DARTFD
1 7 DARTFDJ
1 8 CRFDSPB
1 9 CRFD 1
1 10 SIDCUP
1 11 LEEE
1 12 HTHRGRN
2 0 HULL 7
2 1 HESSLE 1
2 ...
I currently use the following SQL to get the pairs of Tiplocs (without platforms)
select distinct TIPLOC as A, next_location as B
from (select st.*,
lead(TIPLOC) over (partition by HeaderId order by LocationOrdinal) as next_location
from sched_timings st
) st
where next_location is not null;
Current Output
A B
GRAINEW GRAIN
GRAIN CLFFD12
CLFFD12 HOOJ
HOOJ GRVSEND
GRVSEND SPHEADJ
SPHEADJ DARTFD
DARTFD DARTFDJ
DARTFDJ CRFDSPB
CRFDSPB CRFD
CRFD SIDCUP
Desired Output
A APlat B BPlat
GRAINEW 1 GRAIN A
GRAIN A CLFFD12
CLFFD12 HOOJ 2
...
UPDATE: I have done the code below which seems to work but not sure if there's a more efficient approach.
select distinct A, AP, next_location as B, next_location_plat as BP
from (select st.TIPLOC as A, st.Platform as AP,
lead(TIPLOC) over (partition by HeaderId order by LocationOrdinal) as next_location,
lead(Platform) over (partition by HeaderId order by LocationOrdinal) as next_location_plat
from sched_timings st
) st
where next_location is not null;