Re: reporting tree into separate columns - Mailing list pgsql-novice

I would try the following, if I understood correctly

1- define your query as a CTE (common table expression) call it family

2- in the select using this CTE, add 3 columns with a case as in 
select
case when depth = 0 then jina
else '' end as jina,

case when depth = 1 then jina

else '' end as jina_1,

case when depth = 2 then jina

else '' end as jina_2

from family 

Order by jina, depth

Just from the top of my head, the syntax could be wrong

Kind regards
Didier

On Sat, 25 Nov 2023 at 08:40, Ibrahim Shaame <ishaame@gmail.com> wrote:

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