reporting tree into separate columns

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

Responses

Browse pgsql-novice by date

  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