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
|
| Список | 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,
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
Order by jina, depth
Just from the top of my head, the syntax could be wrong
Kind regards
Didier
Didier
On Sat, 25 Nov 2023 at 08:40, Ibrahim Shaame <ishaame@gmail.com> wrote:
I have the following query which gives me family treewith 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
В списке pgsql-novice по дате отправления: