I am trying to create a query that shows the different levels of the hierarchy in different columns For now it looks like this

I want the output to look like this

CodePudding user response：

You can use a recursive query:

WITH hierarchy (root, child, child1, child2, child3, child4, child5, lvl) AS (
  SELECT parent,
         child,
         child,
         CAST(NULL AS NUMBER),
         CAST(NULL AS NUMBER),
         CAST(NULL AS NUMBER),
         CAST(NULL AS NUMBER),
         1
  FROM   table_name
  WHERE  parent = 1
UNION ALL
  SELECT h.root,
         t.child,
         h.child1,
         CASE lvl   1 WHEN 2 THEN t.child ELSE h.child2 END,
         CASE lvl   1 WHEN 3 THEN t.child ELSE h.child3 END,
         CASE lvl   1 WHEN 4 THEN t.child ELSE h.child4 END,
         CASE lvl   1 WHEN 5 THEN t.child ELSE h.child5 END,
         lvl   1
  FROM   hierarchy h
         LEFT OUTER JOIN table_name t
         ON (h.child = t.parent)
  WHERE  lvl < 5
)
CYCLE root, child1, child2, child3, child4, child5, lvl SET is_cycle TO 1 DEFAULT 0
SELECT root,
       child1,
       child2,
       child3,
       child4,
       child5
FROM   hierarchy
WHERE  lvl = 5

or:

SELECT REGEXP_SUBSTR(path, '[^|] ', 1, 1) AS root,
       REGEXP_SUBSTR(path, '[^|] ', 1, 2) AS child1,
       REGEXP_SUBSTR(path, '[^|] ', 1, 3) AS child2,
       REGEXP_SUBSTR(path, '[^|] ', 1, 4) AS child3,
       REGEXP_SUBSTR(path, '[^|] ', 1, 5) AS child4,
       REGEXP_SUBSTR(path, '[^|] ', 1, 6) AS child5
FROM   (
  SELECT CONNECT_BY_ROOT parent || SYS_CONNECT_BY_PATH(child, '|') AS path
  FROM   table_name
  WHERE  CONNECT_BY_ISLEAF = 1
  START WITH parent = 1
  CONNECT BY PRIOR child = parent
)

Which, for the sample data:

CREATE TABLE table_name (Parent, Child) AS
SELECT 1, 2 FROM DUAL UNION ALL
SELECT 1, 3 FROM DUAL UNION ALL
SELECT 2, 4 FROM DUAL UNION ALL
SELECT 2, 5 FROM DUAL UNION ALL
SELECT 3, 5 FROM DUAL UNION ALL
SELECT 4, 6 FROM DUAL UNION ALL
SELECT 6, 7 FROM DUAL UNION ALL
SELECT 7, 8 FROM DUAL;

Both output:

ROOT	CHILD1	CHILD2	CHILD3	CHILD4	CHILD5
1	2	4	6	7	8
1	2	5	null	null	null
1	3	5	null	null	null

db<>fiddle here

CodePudding user response：

A simple self-join does the job:

select
  c1.parent as root,
  c1.child as child1,
  c2.child as child2,
  c3.child as child3,
  c4.child as child4
from mytable c1
left join mytable c2 on c2.parent = c1.child
left join mytable c3 on c3.parent = c2.child
left join mytable c4 on c4.parent = c3.child
where c1.parent not in (select child from mytable)
order by root, child1, child2, child3, child4;

Demo: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=fcc7963188b28b519417142a4b5f70f6