Re: reporting tree into separate columns

Thanks for the response :
...... insert values to proper place in array instead of building
concatenated string separated with ' - '
Any proposal for it. Because there I have not succeeded.




On Sun, Nov 26, 2023 at 2:33 PM Tomek <tomekphotos@gmail.com> wrote:

> Now you can use regexp_split_to_array
> Look like it works:
> select (regexp_split_to_array('Asia Khamis Haji - Ishak Makame Haji -
> Ibrahim Ishak Makame', '-'))[1]
> , (regexp_split_to_array('Asia Khamis Haji - Ishak Makame Haji - Ibrahim
> Ishak Makame', '-'))[2]
> , (regexp_split_to_array('Asia Khamis Haji - Ishak Makame Haji - Ibrahim
> Ishak Makame', '-'))[3]
> , (regexp_split_to_array('Asia Khamis Haji - Ishak Makame Haji - Ibrahim
> Ishak Makame', '-'))[4]
>
> Or from the beginning in your CTE insert values to proper place in array
> instead of building concatenated string separated with ' - '
>
> Regards Tomek
> (szaman)
>
> sob., 25 lis 2023 o 08:40 Ibrahim Shaame <ishaame@gmail.com> napisał(a):
>
>> 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
>>
>