您可以使用分层查询（只查询一次表）：

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;

字符串
或LEFT OUTER JOIN到同一个表（这将查询该表两次）：

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;

型
其中，对于样本数据：

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;

型
两个输出：
| 代码_1|第1类|CODE_2|第二类|代码_3|类别_3|
| --|--|--|--|--|--|
| 21BEMVXP040150FIS4|一|21CYMVXP040152VFO4| B| 23FRDDS2NCNF1LOBR 4| C|
| 21BEMVXP040150FIS9|一|23FRDDS2NCNF1LOBR 9| C| * 空 | 空 *|
| 21CYMVXP040152VFO4| B| 23FRDDS2NCNF1LOBR 4| C| * 空 | 空 *|
| 21DEMVXP040222UJK 5| B| 23FRDDS4NCNF1LOBR4| C| * 空 | 空 *|
| 22NLDDS2ACNF3MQJC 4| B| 21BEMVXP040150FIS9|一|23FRDDS2NCNF1LOBR 9| C|
fiddle