Re: reporting tree into separate columns

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