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 |