Thread

1. reporting tree into separate columns

   Ibrahim Shaame <ishaame@gmail.com> — 2023-11-25T07:39:28Z

   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
   

2. Re: reporting tree into separate columns

   Didier Gasser-Morlay <didiergm@gmail.com> — 2023-11-25T09:54:30Z

   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:
   >
   >
   > 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
   >
   

3. Re: reporting tree into separate columns

   Ibrahim Shaame <ishaame@gmail.com> — 2023-11-25T10:38:44Z

   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:
   >>
   >>
   >> 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
   >>
   >
   

4. Re: reporting tree into separate columns

   David G. Johnston <david.g.johnston@gmail.com> — 2023-11-25T14:02:44Z

   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.
   

5. Re: reporting tree into separate columns

   Tomek <tomekphotos@gmail.com> — 2023-11-26T11:33:41Z

   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:
   >
   >
   > 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
   >
   

6. Re: reporting tree into separate columns

   Ibrahim Shaame <ishaame@gmail.com> — 2023-11-26T18:18:42Z

   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:
   >>
   >>
   >> 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
   >>
   >