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

Please, show the code you have so far: what did you try, what was the issue with it? Do you have a variable number of columns or they are limited by some fixed possible number? Also there're actually two questions: about hierarchy and about date filtering (when date field is not present in source data). Please, ask only one problem per question. — astentx, May 07 '21 at 10:06
Thank you for the comment and the suggestion, I removed the data filtering part, now I'm editing the question adding the query I tried. — antoninus96, May 07 '21 at 10:13

score 0 · Answer 1 · answered May 07 '21 at 10:16

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

OracleSQL: generate a horizontal view from father-son table

1 Answers1