From: | Ibrahim Shaame <ishaame(at)gmail(dot)com> |
---|---|
To: | pgsql-novice(at)lists(dot)postgresql(dot)org |
Subject: | reporting tree into separate columns |
Date: | 2023-11-25 07:39:28 |
Message-ID: | CAJOWwD615LZV9+cppcVdajXJpn25VnjCmymMZrQxdwpfU9Ccsg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
I have the following query which gives me family tree
with recursive x (jina,namba,nasaba_1)
as (
select jina ||' '|| baba ||' '|| babu AS jina,namba, nasaba_1
from majina2
where nasaba_1 = 0
union all
select x.jina ||' '|| ' - '|| e.jina || ' ' || baba || ' ' || babu,
e.namba, e.nasaba_1
from majina2 e, x
where e.nasaba_1 = x.namba
)
select
jina,namba,nasaba_1,(length(jina)-length(replace(jina,'-','')))/length('-')
AS depth
from x
order by 1;
And I get the following result:
jina namba Nasaba_1 depth
Asia Khamis Haji 100002 0 0
Asia Khamis Haji - Azida Makame Haji 100128 100002 1
Asia Khamis Haji - Ishak Makame Haji 100127 100002 1
Asia Khamis Haji - Ishak Makame Haji - Alia Ishak Makame 100250 100127 2
Asia Khamis Haji - Ishak Makame Haji - Ibrahim Ishak Makame 100251 100127 2
Asia Khamis Haji - Khamis Abdalla Ali 100126 100002 1
Asia Khamis Haji - Mwajuma Abdalla 100125 100002 1
Asia Khamis Haji - Namwira Abdalla Mosi 100124 100002 1
But what I want to get is to report the first column in different columns
according to depth (last column)
Any suggestions
Thanks
Ibrahim Shaame
From | Date | Subject | |
---|---|---|---|
Next Message | Didier Gasser-Morlay | 2023-11-25 09:54:30 | Re: reporting tree into separate columns |
Previous Message | Tom Lane | 2023-11-23 15:47:45 | Re: Run a transaction block through SPI_execute |