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