Обсуждение: reporting tree into separate columns

Поиск
Список
Период
Сортировка

reporting tree into separate columns

От
Ibrahim Shaame
Дата:
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:


jinanambaNasaba_1depth
Asia Khamis Haji 10000200
Asia Khamis Haji - Azida Makame Haji 1001281000021
Asia Khamis Haji - Ishak Makame Haji 1001271000021
Asia Khamis Haji - Ishak Makame Haji - Alia Ishak Makame 1002501001272
Asia Khamis Haji - Ishak Makame Haji - Ibrahim Ishak Makame 1002511001272
Asia Khamis Haji - Khamis Abdalla Ali 1001261000021
Asia Khamis Haji - Mwajuma Abdalla 1001251000021
Asia Khamis Haji - Namwira Abdalla Mosi 1001241000021


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

Re: reporting tree into separate columns

От
Didier Gasser-Morlay
Дата:
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 <ishaame@gmail.com> 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:


jinanambaNasaba_1depth
Asia Khamis Haji 10000200
Asia Khamis Haji - Azida Makame Haji 1001281000021
Asia Khamis Haji - Ishak Makame Haji 1001271000021
Asia Khamis Haji - Ishak Makame Haji - Alia Ishak Makame 1002501001272
Asia Khamis Haji - Ishak Makame Haji - Ibrahim Ishak Makame 1002511001272
Asia Khamis Haji - Khamis Abdalla Ali 1001261000021
Asia Khamis Haji - Mwajuma Abdalla 1001251000021
Asia Khamis Haji - Namwira Abdalla Mosi 1001241000021


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

Re: reporting tree into separate columns

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

On Sat, Nov 25, 2023 at 12:54 PM Didier Gasser-Morlay <didiergm@gmail.com> 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 <ishaame@gmail.com> 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:


jinanambaNasaba_1depth
Asia Khamis Haji 10000200
Asia Khamis Haji - Azida Makame Haji 1001281000021
Asia Khamis Haji - Ishak Makame Haji 1001271000021
Asia Khamis Haji - Ishak Makame Haji - Alia Ishak Makame 1002501001272
Asia Khamis Haji - Ishak Makame Haji - Ibrahim Ishak Makame 1002511001272
Asia Khamis Haji - Khamis Abdalla Ali 1001261000021
Asia Khamis Haji - Mwajuma Abdalla 1001251000021
Asia Khamis Haji - Namwira Abdalla Mosi 1001241000021


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

Re: reporting tree into separate columns

От
"David G. Johnston"
Дата:
On Saturday, November 25, 2023, Ibrahim Shaame <ishaame@gmail.com> 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.

 

Re: reporting tree into separate columns

От
Tomek
Дата:
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 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:


jinanambaNasaba_1depth
Asia Khamis Haji 10000200
Asia Khamis Haji - Azida Makame Haji 1001281000021
Asia Khamis Haji - Ishak Makame Haji 1001271000021
Asia Khamis Haji - Ishak Makame Haji - Alia Ishak Makame 1002501001272
Asia Khamis Haji - Ishak Makame Haji - Ibrahim Ishak Makame 1002511001272
Asia Khamis Haji - Khamis Abdalla Ali 1001261000021
Asia Khamis Haji - Mwajuma Abdalla 1001251000021
Asia Khamis Haji - Namwira Abdalla Mosi 1001241000021


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

Re: reporting tree into separate columns

От
Ibrahim Shaame
Дата:
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 <tomekphotos@gmail.com> 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 <ishaame@gmail.com> 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:


jinanambaNasaba_1depth
Asia Khamis Haji 10000200
Asia Khamis Haji - Azida Makame Haji 1001281000021
Asia Khamis Haji - Ishak Makame Haji 1001271000021
Asia Khamis Haji - Ishak Makame Haji - Alia Ishak Makame 1002501001272
Asia Khamis Haji - Ishak Makame Haji - Ibrahim Ishak Makame 1002511001272
Asia Khamis Haji - Khamis Abdalla Ali 1001261000021
Asia Khamis Haji - Mwajuma Abdalla 1001251000021
Asia Khamis Haji - Namwira Abdalla Mosi 1001241000021


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