I need to make sure we have support to create a hierarchy table based on a "father/son" table. Example:
| F | S |
|---|---|
| a | b |
| b | c |
| b | d |
| b | e |
| e | f |
| e | g |
| b | m |
| z | n |
| m | k |
Expected result:
| L | L1 | L2 | L3 |
|---|---|---|---|
| a | b | c | |
| a | b | d | |
| a | b | e | |
| e | f | ||
| e | g | ||
| a | b | m | k |
| z | n |
Any idea, suggestion?
Thank you
with
recursive cte(id, path) as
(
select f, f::text from t where f not in(select s from t where s is not null)
union all
select t.s, cte.path || '/' || t.s from cte join t on cte.id = t.f
)
select id
,path[1] as root
,path[2] as l1
,path[3] as l2
,path[4] as l3
,path[5] as l4
from
(
select id
,regexp_split_to_array(path, '/') as path
from cte
) cte
| id | root | l1 | l2 | l3 | l4 |
|---|---|---|---|---|---|
| a | a | null | null | null | null |
| z | z | null | null | null | null |
| b | a | b | null | null | null |
| n | z | n | null | null | null |
| m | a | b | m | null | null |
| e | a | b | e | null | null |
| d | a | b | d | null | null |
| c | a | b | c | null | null |
| k | a | b | m | k | null |
| g | a | b | e | g | null |
| f | a | b | e | f | null |