Thread: reporting tree into separate columns
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
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,
Order by jina, depth
Didier
I have the following query which gives me family treewith 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
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 jinaelse '' end as jina_1,case when depth = 2 then jinaelse '' end as jina_2from family
Order by jina, depthJust from the top of my head, the syntax could be wrongKind regards
DidierOn Sat, 25 Nov 2023 at 08:40, Ibrahim Shaame <ishaame@gmail.com> wrote:I have the following query which gives me family treewith 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
Thank you Didier for the response. But I can't see the solution there you propose would give me the same thing (jina). Remember that the column "jina" was obtained as a result of displaying a family tree. Now what I would like is break out this column into a number of columns depending on the depth.Any suggestions?
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]
I have the following query which gives me family treewith 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
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 treewith 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