Re: reporting tree into separate columns

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(at)gmail(dot)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
>