Hierarchy formatting

Question

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

Parent	Child
1	2
1	3
2	4
2	5
3	5

I want the output to look like this

Root	Child1	Child2
1	2	4
1	2	5
1	3	5

Hi - you probably need to format, in your question, the source data and the result you want to achieve so that it is understandable — NickW, Jan 30 '22 at 20:27
Since SQL includes data definition, a [mcve] should include [DDL](//en.wikipedia.org/wiki/Data_definition_language) statements for sample tables (rather than an ad hoc table specification) and [DML](//en.wikipedia.org/wiki/Data_manipulation_language) statements for sample data (rather than a dump or ad hoc format). See the help center for more on [how to ask good questions](//stackoverflow.com/help/how-to-ask) and many other helpful topics. — outis, Jan 31 '22 at 13:55
The question needs a better description of the problem. Before any of that, however, you should search the site to check that the question hasn't been asked before. — outis, Jan 31 '22 at 13:57

MT0 · Answer 1 · 2022-01-30T20:58:46.073

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

You even got an extra level beyond what the OP wanted. – The Impaler Jan 30 '22 at 20:48 — The Impaler, Jan 30 '22 at 20:48

score 1 · Answer 2 · answered Jan 30 '22 at 20:55

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

Hierarchy formatting

2 Answers2