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