hierarchical table connect left element with right

Question

I have a problem with creating Hierarchical table,

I have the following data

    With 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 UNION ALL
    )
    SELECT *
      FROM data;

I want to connect the left_code with right_code

The desired output should look like this:

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

Could you help me please ?

Answer 1

You can use a hierarchical query (which will only query the table once):

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;

or a LEFT OUTER JOIN to the same table (which will query the table twice):

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;

Which, for the sample data:

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;

Both output:

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

fiddle