Subject: reporting tree into separate columns

From: Ibrahim Shaame <[email-masked]>
Date: Sat, 25 Nov 2023 10:39:28 +0300

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

---

From: Didier Gasser-Morlay <[email-masked]>
Date: Sat, 25 Nov 2023 10:54:30 +0100

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

---

From: Ibrahim Shaame <[email-masked]>
Date: Sat, 25 Nov 2023 13:38:44 +0300

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?
Thanks
Ibrahim

---

From: "David G. Johnston" <[email-masked]>
Date: Sat, 25 Nov 2023 07:02:44 -0700

> would like is break out this column into a number of columns depending on

Pull the data into your langauge of choice and build out the presentation
there.  SQL doesn’t do dynamic columns from data very well.

David J.

---

From: Tomek <[email-masked]>
Date: Sun, 26 Nov 2023 12:33:41 +0100

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)

---

From: Ibrahim Shaame <[email-masked]>
Date: Sun, 26 Nov 2023 21:18:42 +0300

Thanks for the response :
...... insert values to proper place in array instead of building
concatenated string separated with ' - '
Any proposal for it. Because there I have not succeeded.