Oracle 11g Release 2

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

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