public inbox for [email protected]
help / color / mirror / Atom feedreporting tree into separate columns
6+ messages / 4 participants
[nested] [flat]
* reporting tree into separate columns
@ 2023-11-25 07:39 Ibrahim Shaame <[email protected]>
0 siblings, 2 replies; 6+ messages in thread
From: Ibrahim Shaame @ 2023-11-25 07:39 UTC (permalink / raw)
To: [email protected]
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: reporting tree into separate columns
@ 2023-11-25 09:54 Didier Gasser-Morlay <[email protected]>
parent: Ibrahim Shaame <[email protected]>
1 sibling, 1 reply; 6+ messages in thread
From: Didier Gasser-Morlay @ 2023-11-25 09:54 UTC (permalink / raw)
To: Ibrahim Shaame <[email protected]>; +Cc: [email protected]
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
On Sat, 25 Nov 2023 at 08:40, Ibrahim Shaame <[email protected]> wrote:
> 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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: reporting tree into separate columns
@ 2023-11-25 10:38 Ibrahim Shaame <[email protected]>
parent: Didier Gasser-Morlay <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Ibrahim Shaame @ 2023-11-25 10:38 UTC (permalink / raw)
To: Didier Gasser-Morlay <[email protected]>; +Cc: [email protected]
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
On Sat, Nov 25, 2023 at 12:54 PM Didier Gasser-Morlay <[email protected]>
wrote:
> 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
>
>
>
>
>
> On Sat, 25 Nov 2023 at 08:40, Ibrahim Shaame <[email protected]> wrote:
>
>> 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
>>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: reporting tree into separate columns
@ 2023-11-25 14:02 David G. Johnston <[email protected]>
parent: Ibrahim Shaame <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: David G. Johnston @ 2023-11-25 14:02 UTC (permalink / raw)
To: Ibrahim Shaame <[email protected]>; +Cc: Didier Gasser-Morlay <[email protected]>; [email protected] <[email protected]>
On Saturday, November 25, 2023, Ibrahim Shaame <[email protected]> wrote:
> 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?
>
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: reporting tree into separate columns
@ 2023-11-26 11:33 Tomek <[email protected]>
parent: Ibrahim Shaame <[email protected]>
1 sibling, 1 reply; 6+ messages in thread
From: Tomek @ 2023-11-26 11:33 UTC (permalink / raw)
To: Ibrahim Shaame <[email protected]>; +Cc: [email protected]
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 <[email protected]> napisał(a):
> 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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: reporting tree into separate columns
@ 2023-11-26 18:18 Ibrahim Shaame <[email protected]>
parent: Tomek <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Ibrahim Shaame @ 2023-11-26 18:18 UTC (permalink / raw)
To: Tomek <[email protected]>; +Cc: [email protected]
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.
On Sun, Nov 26, 2023 at 2:33 PM Tomek <[email protected]> wrote:
> 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 <[email protected]> napisał(a):
>
>> 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
>>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2023-11-26 18:18 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-11-25 07:39 reporting tree into separate columns Ibrahim Shaame <[email protected]>
2023-11-25 09:54 ` Didier Gasser-Morlay <[email protected]>
2023-11-25 10:38 ` Ibrahim Shaame <[email protected]>
2023-11-25 14:02 ` David G. Johnston <[email protected]>
2023-11-26 11:33 ` Tomek <[email protected]>
2023-11-26 18:18 ` Ibrahim Shaame <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox