Re: reporting tree into separate columns

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