Oracle 23c

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 data( left_code, left_category, right_code, right_category ) as
  SELECT '21BEMVXP040150FIS4', 'A', '21CYMVXP040152VFO4', 'B' FROM DUAL UNION ALL
  SELECT '21CYMVXP040152VFO4', 'B', '23FRDDS2NCNF1LOBR4', 'C' FROM DUAL UNION ALL
  SELECT '22NLDDS2ACNF3MQJC4', 'B', '21BEMVXP040150FIS9', 'A' FROM DUAL UNION ALL
  SELECT '21BEMVXP040150FIS9', 'A', '23FRDDS2NCNF1LOBR9', 'C' FROM DUAL UNION ALL
  SELECT '21DEMVXP040222UJK5', 'B', '23FRDDS4NCNF1LOBR4', 'C' FROM DUAL;
5 rows affected

SELECT CONNECT_BY_ROOT left_code AS code_1,
       CONNECT_BY_ROOT left_category AS category_1,
       CONNECT_BY_ROOT right_code AS code_2,
       CONNECT_BY_ROOT right_category AS category_2,
       CASE LEVEL WHEN 2 THEN right_code END AS code_3,
       CASE LEVEL WHEN 2 THEN right_category END AS category_3
FROM   data
WHERE  LEVEL = 2
OR     (LEVEL = 1 AND CONNECT_BY_ISLEAF = 1)
CONNECT BY PRIOR right_code = left_code;


CODE_1
CATEGORY_1
CODE_2
CATEGORY_2
CODE_3
CATEGORY_3


21BEMVXP040150FIS4
A
21CYMVXP040152VFO4
B
23FRDDS2NCNF1LOBR4
C

21BEMVXP040150FIS9
A
23FRDDS2NCNF1LOBR9
C
null
null

21CYMVXP040152VFO4
B
23FRDDS2NCNF1LOBR4
C
null
null

21DEMVXP040222UJK5
B
23FRDDS4NCNF1LOBR4
C
null
null

22NLDDS2ACNF3MQJC4
B
21BEMVXP040150FIS9
A
23FRDDS2NCNF1LOBR9
C


SELECT d1.left_code AS code_1,
       d1.left_category AS category_1,
       d1.right_code AS code_2,
       d1.right_category AS category_2,
       d2.right_code AS code_3,
       d2.right_category AS category_3
FROM   data d1
       LEFT OUTER JOIN data d2
       ON d1.right_code = d2.left_code;


CODE_1
CATEGORY_1
CODE_2
CATEGORY_2
CODE_3
CATEGORY_3


21BEMVXP040150FIS4
A
21CYMVXP040152VFO4
B
23FRDDS2NCNF1LOBR4
C

22NLDDS2ACNF3MQJC4
B
21BEMVXP040150FIS9
A
23FRDDS2NCNF1LOBR9
C

21BEMVXP040150FIS9
A
23FRDDS2NCNF1LOBR9
C
null
null

21CYMVXP040152VFO4
B
23FRDDS2NCNF1LOBR4
C
null
null

21DEMVXP040222UJK5
B
23FRDDS4NCNF1LOBR4
C
null
null

CODE_1	CATEGORY_1	CODE_2	CATEGORY_2	CODE_3	CATEGORY_3
21BEMVXP040150FIS4	A	21CYMVXP040152VFO4	B	23FRDDS2NCNF1LOBR4	C
21BEMVXP040150FIS9	A	23FRDDS2NCNF1LOBR9	C	null	null
21CYMVXP040152VFO4	B	23FRDDS2NCNF1LOBR4	C	null	null
21DEMVXP040222UJK5	B	23FRDDS4NCNF1LOBR4	C	null	null
22NLDDS2ACNF3MQJC4	B	21BEMVXP040150FIS9	A	23FRDDS2NCNF1LOBR9	C