OracleSQL: generate a horizontal view from father-son table

Question

I have a table with this structure:

• ELEMENT
• FATHER_ELEMENT
• INITIAL_DATE
• END_DATE

I can have this situation:

| ELEMENT| FATHER_ELEMENT|
|--------|---------------|
| A      | B             |
| A      | C             |
| B      | D             |
| D      | E             |
| X      | Y             |

And I'd like to have this output:

| LIV0| LIV1|LIV2| LIV3| LIV4|
|-----|-----|----|-----|-----|
| A   | B   |D   |E    |E    |
| A   | C   |C   |C    |C    |
| B   | D   |D   |D    |D    |
| X   | Y   |Y   |Y    |Y    |

Basically I want to generate some rows starting from all the fathers and having 1 record for each path until I reach a different leaf, I want it using 12 level (in the example there are 5 levels) and, If for example, for a path, I have deep = 4, the output should have replicate the leaf until the last level is reached.

How can I obtain that result?

Thank you

Answer 1

You can use a recursive sub-query factoring clause:

WITH levels ( liv0, liv1, liv2, liv3, liv4, lvl ) AS (
  SELECT element, father_element, null, null, null, 1 FROM table_name
UNION ALL
  SELECT liv0,
         liv1,
         CASE l.lvl
         WHEN 1 THEN COALESCE( t.father_element, l.liv1 )
         ELSE l.liv2
         END,
         CASE l.lvl
         WHEN 2 THEN COALESCE( t.father_element, l.liv2 )
         ELSE l.liv3
         END,
         CASE l.lvl
         WHEN 3 THEN COALESCE( t.father_element, l.liv3 )
         ELSE l.liv4
         END,
         l.lvl + 1
  FROM   levels l
         LEFT OUTER JOIN table_name t
         ON ( CASE l.lvl
              WHEN 1 THEN l.liv1
              WHEN 2 THEN l.liv2
              WHEN 3 THEN l.liv3
              END = t.element )
  WHERE  l.lvl < 4
)
SELECT liv0,
       liv1,
       liv2,
       liv3,
       liv4
FROM   levels
WHERE  lvl = 4

Which, for the sample data:

CREATE TABLE table_name ( ELEMENT, FATHER_ELEMENT ) AS
SELECT 'A', 'B' FROM DUAL UNION ALL
SELECT 'A', 'C' FROM DUAL UNION ALL
SELECT 'B', 'D' FROM DUAL UNION ALL
SELECT 'D', 'E' FROM DUAL UNION ALL
SELECT 'X', 'Y' FROM DUAL;

Outputs:

LIV0	LIV1	LIV2	LIV3	LIV4
A	C	C	C	C
A	B	D	E	E
B	D	E	E	E
D	E	E	E	E
X	Y	Y	Y	Y

db<>fiddle here