public inbox for [email protected]  

help / color / mirror / Atom feed

From: Ibrahim Shaame <[email protected]>
To: [email protected]
Subject: reporting tree into separate columns
Date: Sat, 25 Nov 2023 10:39:28 +0300
Message-ID: <CAJOWwD615LZV9+cppcVdajXJpn25VnjCmymMZrQxdwpfU9Ccsg@mail.gmail.com> (raw)

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]
  Subject: Re: reporting tree into separate columns
  In-Reply-To: <CAJOWwD615LZV9+cppcVdajXJpn25VnjCmymMZrQxdwpfU9Ccsg@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