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