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 1r6q4s-002qtX-Mr for pgsql-novice@arkaria.postgresql.org; Sat, 25 Nov 2023 10:39:58 +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 1r6q4r-001eqp-23 for pgsql-novice@arkaria.postgresql.org; Sat, 25 Nov 2023 10:39:57 +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 1r6q4q-001eqh-Fj for pgsql-novice@lists.postgresql.org; Sat, 25 Nov 2023 10:39:56 +0000 Received: from mail-ej1-x630.google.com ([2a00:1450:4864:20::630]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1r6q4n-007HXD-L0 for pgsql-novice@lists.postgresql.org; Sat, 25 Nov 2023 10:39:54 +0000 Received: by mail-ej1-x630.google.com with SMTP id a640c23a62f3a-a00a9c6f1e9so390188166b.3 for ; Sat, 25 Nov 2023 02:39:53 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1700908791; x=1701513591; 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=O5olOTc8HZkMPt+X3AJiZdIqxVkQHDKemPV+7JCoJ4A=; b=KbVPhUQppP11eD+1CU2f89CPbB+WyJc0gUzXTtq6fxn270IOhHGE2G8HNKoPgCFm3s 39s8HZy82CYzuRkYU63a5BPorsmGGBBbJswRdj0iNvFAdsUNqQT6P/KMYPtano0t/ZqI S1/73Zrn+cPyXj3CF4TgmfAbj0hLbyP4/c7sl+rtiJXWqVI1Qny3Gr9tuKdU5V96fbFg ankcJN5oZKR7RsmBKL8pYc1aNk1AA8cyHEA7AysFdYwYxQSVPLrictgovXOUomSifv5w rv8fAbABzjJQKHGFwyHMBsLUpj8+yP0bqCiIN91q2hPQNnTJCvLMpWIxiL+zM24F2lob /C8A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1700908791; x=1701513591; 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=O5olOTc8HZkMPt+X3AJiZdIqxVkQHDKemPV+7JCoJ4A=; b=EHMUPF8UAPF2ptU6P+AJFr1TMeOZgWiqfeb3Ndwhofae0pchr0b/BoGBjYih/Dj6pG rweYL4Soz77CACw/O94RQCzaFQaG+aaDtLxEIz5iAh/0GHYsjhJdTJH2yFJt0qIVuUP3 teRssSxaSn/9dmgCP0BUpp4fs/XsXPBKHmd10Mk3A7Q0c2Lo4ZWxhrsb2BbNsA6b+rpP OWbkpVCEglfYk+RXynty7HQStP/nqarHJCw5qWJVkLY86Uin7Ae1mcYDBUadCF3Howk4 O2pWYd82bCqVCDIfkelNEnm76pnyRagu22RzHVjNHKtl3dSidIysDXYDe+gY9RBf4QZs qg+w== X-Gm-Message-State: AOJu0YwGbAyOIsg5O7Rd28tWopQ9PLyOwnpj33BNliGbB9WSRqFe56rU 6fHP+AnjfhOgvhInI0G9kkF/yeKyzvYD9oG3WMU= X-Google-Smtp-Source: AGHT+IEL/dTkOJb7WVGj1CrK2+zOCpw0ScIgDnf3WA5mij5pu9pwtzfwOKjsZQbATEBMiM9C0zKMtqOZ7bl4fVglfYk= X-Received: by 2002:a17:906:207:b0:a0b:6873:45b1 with SMTP id 7-20020a170906020700b00a0b687345b1mr1299259ejd.39.1700908790908; Sat, 25 Nov 2023 02:39:50 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Ibrahim Shaame Date: Sat, 25 Nov 2023 13:38:44 +0300 Message-ID: Subject: Re: reporting tree into separate columns To: Didier Gasser-Morlay Cc: pgsql-novice@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000470e86060af7b041" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000470e86060af7b041 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Thank you Didier for the response. But I can't see the solution there you propose would give me the same thing (jina). Remember that the column "jina" was obtained as a result of displaying a family tree. Now what I would like is break out this column into a number of columns depending on the depth. Any suggestions? Thanks Ibrahim On Sat, Nov 25, 2023 at 12:54=E2=80=AFPM Didier Gasser-Morlay wrote: > 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 =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 > > 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 =3D 0 >> >> union all >> >> select x.jina ||' '|| ' - '|| e.jina || ' ' || baba || ' ' || babu, >> e.namba, e.nasaba_1 >> >> from majina2 e, x >> >> where e.nasaba_1 =3D 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 10012= 7 >> 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 column= s >> according to depth (last column) >> >> >> Any suggestions >> >> >> Thanks >> >> Ibrahim Shaame >> > --000000000000470e86060af7b041 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Thank you Didier for the response. But I can't se= e the solution there you propose would give me the same thing (jina). Remem= ber that the column "jina" was obtained as a result of displaying= a family tree. Now what I would like is break out this column into a numbe= r of columns depending on the depth.

Any suggestio= ns?
Thanks
Ibrahim

On Sat, Nov 25, 2023 at 12:= 54=E2=80=AFPM Didier Gasser-Morlay <didiergm@gmail.com> wrote:
I would try the following, if I unders= tood=C2=A0correctly

1- define your query as a CTE (common table expr= ession) call it family

2- in the select using this CTE, add 3 column= s with a case as in=C2=A0
select
case when depth =3D 0 then jina
e= lse '' 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
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

=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>

--000000000000470e86060af7b041--