I have this kind of data on a Oracle SQL DB:
personId lastEvent currentEvent nextEvent
1 null 1 2
1 1 2 3
1 2 3 4
1 3 4 null
For a single row like
personId lastEvent currentEvent nextEvent
1 null 1 2
I want to extract the two rows extracted by combining together the events columns and categorizing these two rows to the specific currentEvent. For example:
Row #1
personId lastEvent currentEvent nextEvent
1 null 1 2
Result #1
personId event1 event2 currentEvent
1 null 1 1
1 1 2 1
Row #2
personId lastEvent currentEvent nextEvent
1 1 2 3
Result #2
personId event1 event2 currentEvent
1 1 2 2
1 2 3 2
etc..... I know the existence of the clause CONNECT BY but I'm not really able to figure out the query to obtain these results.
This is not a hierarchical query at all: you merely want to split each input row into two output rows and apply a little logic.
An easy way to perform the split is to CROSS JOIN your table to a row source that has exactly two rows.
E.g.,
with input_data ( personId, lastEvent, currentEvent, nextEvent) AS
(
SELECT 1, null, 1, 2 FROM DUAL UNION ALL
SELECT 1, 1, 2, 3 FROM DUAL UNION ALL
SELECT 1, 2, 3, 4 FROM DUAL UNION ALL
SELECT 1, 3, 4, null FROM DUAL )
SELECT personId,
decode(rn, 1, lastEvent, 2, currentEvent) event1,
decode(rn, 1, currentEvent, 2, nextEvent) event2,
currentEvent
from input_data
CROSS JOIN ( SELECT rownum rn FROM DUAL CONNECT BY ROWNUM <= 2 ) r
ORDER BY personId, currentEvent, rn;
+----------+--------+--------+--------------+ | PERSONID | EVENT1 | EVENT2 | CURRENTEVENT | +----------+--------+--------+--------------+ | 1 | | 1 | 1 | | 1 | 1 | 2 | 1 | | 1 | 1 | 2 | 2 | | 1 | 2 | 3 | 2 | | 1 | 2 | 3 | 3 | | 1 | 3 | 4 | 3 | | 1 | 3 | 4 | 4 | | 1 | 4 | | 4 | +----------+--------+--------+--------------+