public inbox for [email protected]
help / color / mirror / Atom feedFrom: Didier Gasser-Morlay <[email protected]>
To: Ibrahim Shaame <[email protected]>
Cc: [email protected]
Subject: Re: reporting tree into separate columns
Date: Sat, 25 Nov 2023 10:54:30 +0100
Message-ID: <CAF5nyP9q=dFFtVm13Ni_sWOj-EY3cKRcZWEVbB=vLXbQ0j+wvQ@mail.gmail.com> (raw)
In-Reply-To: <CAJOWwD615LZV9+cppcVdajXJpn25VnjCmymMZrQxdwpfU9Ccsg@mail.gmail.com>
References: <CAJOWwD615LZV9+cppcVdajXJpn25VnjCmymMZrQxdwpfU9Ccsg@mail.gmail.com>
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
>
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: <CAF5nyP9q=dFFtVm13Ni_sWOj-EY3cKRcZWEVbB=vLXbQ0j+wvQ@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