public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tomek <[email protected]>
To: Ibrahim Shaame <[email protected]>
Cc: [email protected]
Subject: Re: reporting tree into separate columns
Date: Sun, 26 Nov 2023 12:33:41 +0100
Message-ID: <CACUaW3Qd6+TH9Gw0UhvJGw+LYU5548k7QbViAKMXox=OuskB5Q@mail.gmail.com> (raw)
In-Reply-To: <CAJOWwD615LZV9+cppcVdajXJpn25VnjCmymMZrQxdwpfU9Ccsg@mail.gmail.com>
References: <CAJOWwD615LZV9+cppcVdajXJpn25VnjCmymMZrQxdwpfU9Ccsg@mail.gmail.com>

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
>

view thread (6+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: reporting tree into separate columns
  In-Reply-To: <CACUaW3Qd6+TH9Gw0UhvJGw+LYU5548k7QbViAKMXox=OuskB5Q@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox