public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ibrahim Shaame <[email protected]>
To: Tomek <[email protected]>
Cc: [email protected]
Subject: Re: reporting tree into separate columns
Date: Sun, 26 Nov 2023 21:18:42 +0300
Message-ID: <CAJOWwD7fnwOzw7rJG=u8RQXg8ghhX=cepOatmVX9ocX4i5AYjg@mail.gmail.com> (raw)
In-Reply-To: <CACUaW3Qd6+TH9Gw0UhvJGw+LYU5548k7QbViAKMXox=OuskB5Q@mail.gmail.com>
References: <CAJOWwD615LZV9+cppcVdajXJpn25VnjCmymMZrQxdwpfU9Ccsg@mail.gmail.com>
<CACUaW3Qd6+TH9Gw0UhvJGw+LYU5548k7QbViAKMXox=OuskB5Q@mail.gmail.com>
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
>>
>
view thread (6+ messages)
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: <CAJOWwD7fnwOzw7rJG=u8RQXg8ghhX=cepOatmVX9ocX4i5AYjg@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