By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE table_name ("order", part, sn) AS
SELECT 'aa-1', 'A1', '112233' FROM DUAL UNION ALL
SELECT 'aa-1', 'A2', '112234' FROM DUAL UNION ALL
SELECT 'aa-1', 'B1', 'bb-1' FROM DUAL UNION ALL
SELECT 'bb-1', 'S1', '998877' FROM DUAL UNION ALL
SELECT 'bb-1', 'S2', '998878' FROM DUAL UNION ALL
SELECT 'bb-1', 'C1', 'cc-1' FROM DUAL UNION ALL
SELECT 'cc-1', 'X1', '998877' FROM DUAL UNION ALL
SELECT 'cc-1', 'X2', '998878' FROM DUAL;
8 rows affected
SELECT "order",
part,
sn,
CONNECT_BY_ROOT "order" AS parent
FROM table_name
START WITH "order" = 'aa-1'
CONNECT BY PRIOR sn = "order"
| order | PART | SN | PARENT |
|---|---|---|---|
| aa-1 | A1 | 112233 | aa-1 |
| aa-1 | A2 | 112234 | aa-1 |
| aa-1 | B1 | bb-1 | aa-1 |
| bb-1 | S1 | 998877 | aa-1 |
| bb-1 | S2 | 998878 | aa-1 |
| bb-1 | C1 | cc-1 | aa-1 |
| cc-1 | X1 | 998877 | aa-1 |
| cc-1 | X2 | 998878 | aa-1 |
WITH orders ("order", part, sn, parent) AS (
SELECT "order", part, sn, "order"
FROM table_name
WHERE "order" = 'aa-1'
UNION ALL
SELECT t."order", t.part, t.sn, o.parent
FROM orders o
INNER JOIN table_name t
ON o.sn = t."order"
)
SELECT *
FROM orders;
| order | PART | SN | PARENT |
|---|---|---|---|
| aa-1 | A1 | 112233 | aa-1 |
| aa-1 | A2 | 112234 | aa-1 |
| aa-1 | B1 | bb-1 | aa-1 |
| bb-1 | S1 | 998877 | aa-1 |
| bb-1 | S2 | 998878 | aa-1 |
| bb-1 | C1 | cc-1 | aa-1 |
| cc-1 | X1 | 998877 | aa-1 |
| cc-1 | X2 | 998878 | aa-1 |