Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r6pN8-002nwb-It for pgsql-novice@arkaria.postgresql.org; Sat, 25 Nov 2023 09:54:46 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1r6pN7-001St9-2S for pgsql-novice@arkaria.postgresql.org; Sat, 25 Nov 2023 09:54:45 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r6pN6-001St1-Jq for pgsql-novice@lists.postgresql.org; Sat, 25 Nov 2023 09:54:44 +0000 Received: from mail-oa1-x35.google.com ([2001:4860:4864:20::35]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1r6pN4-007HJ2-5F for pgsql-novice@lists.postgresql.org; Sat, 25 Nov 2023 09:54:43 +0000 Received: by mail-oa1-x35.google.com with SMTP id 586e51a60fabf-1fa235f8026so186296fac.3 for ; Sat, 25 Nov 2023 01:54:42 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1700906081; x=1701510881; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=cF9a1Kju3XGtKt0ERLkBZkUPpI58AfVD+zxyhb0qWpo=; b=Nz/7COeZmfwBJVHvRk3kW6d13s4rlC64R6FsEJWAmxXTBNofmsuJzdD7dKC3aFtnOv /GZPUVV+wxIKfSFeFHFQkz/ZFb5CPYUTAE80qL9Cd8Ja6IgPFPknYZNW4DGqQkWi7CB/ sIaVQY35fYARFzM+gfcJuvV305ps77SewSWNPO+HCxYlOK8o60RZVeqiJlGDWLc18gRQ St5giVo40LLZMVQg4nWxN5XktyDKmEhEF/7XFgloQAf9EppxA3DsWFV7iW5xVVAyOWcX diPGTlt3bnJSY0OIqPvS5szaq8TjXMAgy11TGi0G7o8zEFz7lpfKCA0ssTdac3eChyRh yydw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1700906081; x=1701510881; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=cF9a1Kju3XGtKt0ERLkBZkUPpI58AfVD+zxyhb0qWpo=; b=HuxJ+fF0HqyqaZdFAY7v67Q2m2SisqkBa0evCWeTXxtX+jUBE//gdKPvDb1nykV7hV kXLyxCglS5YYd3jcteneSDC3ChzIqTcDHONFLGUi/h+FwTv07Z+pKjnrPmZPaJ2CI3Nx ydKgcEzU09khwuE3IJfwPX5yP15WYvZWMeG7r3uEBedGDO2t8H4HZ/D+UvzqqQRduirG icwyQDeKz5jTDgnQZ2Mk8cRF6WTdehD5SKVd/emLfdrdjvOviib/V5ouyeBFnyWM6caJ qO90S1//Rvp20mc2/KGdgl/GDsBk8n/KuXpo0isLQtOlUXcfe96LgAyGLUIrufPTic4s skVQ== X-Gm-Message-State: AOJu0YyeTETCS4ReCkMamOe6OryVsEiARb7RdEfqLfmEv50XWK2GsZHl xSJcF6ygwDQ6+fgA5hPrQp2hxHmKiFCQfx1JPnU= X-Google-Smtp-Source: AGHT+IGgPxkx3R0F6N3m/sxnFjmaspbo3jJIOocDOsPg8fpi5qfhAiqWzVIWmkA4/ZoiYwaSEcEzawXQOPhympUn/+c= X-Received: by 2002:a05:6870:498a:b0:1f9:36cf:2d4b with SMTP id ho10-20020a056870498a00b001f936cf2d4bmr7172079oab.31.1700906081080; Sat, 25 Nov 2023 01:54:41 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Didier Gasser-Morlay Date: Sat, 25 Nov 2023 10:54:30 +0100 Message-ID: Subject: Re: reporting tree into separate columns To: Ibrahim Shaame Cc: pgsql-novice@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000c25810060af70e11" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000c25810060af70e11 Content-Type: text/plain; charset="UTF-8" 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 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 > --000000000000c25810060af70e11 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
I would try the following, if I understood=C2=A0correctly<= br>
1- define your query as a CTE (common table expression) call it fami= ly

2- in the select using this CTE, add 3 columns with a case as in= =C2=A0
select
case when depth =3D 0 then jina
else '' end = as jina,
case when depth =3D 1 then jina
else '' = end as jina_1,
case when depth =3D 2 then jina
else '' end as jina_2

from family=C2=A0
Order by jina, depth

Just from the top of my= head, the syntax could be wrong

Kind regards
D= idier





On Sat, 25 Nov 2023 at 08:40, Ibrahim Shaame <ishaame@gmail.com> wrote:
I have the following qu= ery which gives me family tree

=09 =09 =09

with recursive x (jina,namba,nasaba_1)

as = (

sel= ect jina ||' '|| baba ||' '= || babu AS jina,namba, nasaba_1

fro= m majina2

whe= re nasaba_1 =3D 0

uni= on all

sel= ect x.jina ||' '|| ' - '|| e.jina || ' ' || baba || ' = 9; || babu, e.namba, e.nasaba_1

fro= m majina2 e, x

wher= e e.nasaba_1 =3D x.namba

)

sele= ct=20 jina,namba,nasaba_1,(length(jina)-length(replace(jina,'-',''= ;)))/length('-') AS depth

from= x

orde= r by 1;


And I get the following result:


=09 =09 =09 =09 =09

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 - Ibrah= im 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 a= ccording to depth (last column)


Any suggestions


Thanks

Ibrahim Shaame<= br>

--000000000000c25810060af70e11--