Re: reporting tree into separate columns

Thank you Didier for the response. But I can't see the solution there you
propose would give me the same thing (jina). Remember that the column
"jina" was obtained as a result of displaying a family tree. Now what I
would like is break out this column into a number of columns depending on
the depth.

Any suggestions?
Thanks
Ibrahim

On Sat, Nov 25, 2023 at 12:54 PM Didier Gasser-Morlay <didiergm(at)gmail(dot)com>
wrote:

> 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
>>
>