Re: reporting tree into separate columns

Поиск
Список
Период
Сортировка
От Didier Gasser-Morlay
Тема Re: reporting tree into separate columns
Дата
Msg-id CAF5nyP9q=dFFtVm13Ni_sWOj-EY3cKRcZWEVbB=vLXbQ0j+wvQ@mail.gmail.com
обсуждение исходный текст
Ответ на reporting tree into separate columns  (Ibrahim Shaame <ishaame@gmail.com>)
Ответы Re: reporting tree into separate columns  (Ibrahim Shaame <ishaame@gmail.com>)
Список pgsql-novice
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:


jinanambaNasaba_1depth
Asia Khamis Haji 10000200
Asia Khamis Haji - Azida Makame Haji 1001281000021
Asia Khamis Haji - Ishak Makame Haji 1001271000021
Asia Khamis Haji - Ishak Makame Haji - Alia Ishak Makame 1002501001272
Asia Khamis Haji - Ishak Makame Haji - Ibrahim Ishak Makame 1002511001272
Asia Khamis Haji - Khamis Abdalla Ali 1001261000021
Asia Khamis Haji - Mwajuma Abdalla 1001251000021
Asia Khamis Haji - Namwira Abdalla Mosi 1001241000021


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

В списке pgsql-novice по дате отправления:

Предыдущее
От: Ibrahim Shaame
Дата:
Сообщение: reporting tree into separate columns
Следующее
От: Ibrahim Shaame
Дата:
Сообщение: Re: reporting tree into separate columns