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 1r6nHS-002eOe-0s for pgsql-novice@arkaria.postgresql.org; Sat, 25 Nov 2023 07:40: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 1r6nHP-000ml8-MD for pgsql-novice@arkaria.postgresql.org; Sat, 25 Nov 2023 07:40:43 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r6nHP-000ml0-CD for pgsql-novice@lists.postgresql.org; Sat, 25 Nov 2023 07:40:43 +0000 Received: from mail-ej1-x632.google.com ([2a00:1450:4864:20::632]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1r6nHI-008QSf-SD for pgsql-novice@lists.postgresql.org; Sat, 25 Nov 2023 07:40:42 +0000 Received: by mail-ej1-x632.google.com with SMTP id a640c23a62f3a-a03a900956dso496465166b.1 for ; Fri, 24 Nov 2023 23:40:36 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1700898035; x=1701502835; darn=lists.postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=+XAGzqmCrfrRg+GA53ir9QmZ0xE00yciyu9uhHpCHaE=; b=SpW0a27fxTuDzAqCz31lfgOEh5pFx4poEDSPNsggjZcsqDl6aJf6n6Jza63wojTq0M yFxyDCDSKItXXa7tSQaVN/+1HFXDxPKBbrTSqA1VQnddxq7Jh5cbwgKwP/o1NnTGo/eU VCPorJkcyUWfFRf+X6n6Z5dB4t4lMW1READs3YSSjfsCtPsjsBokMJfZkD7FNWax256f N6muyhQHbZLOMgrTu8M58576hMowBb7/Yp8tG+1KDrmRne7fhIaB+0ZYI7inn6Zp0vWG BuDYK0lNB6sfBNSiDx5/wYuDaiAwGyQuBz42BeUuJ5W7G5/SPkRgpcTz1581dOE3wo4W 3edg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1700898035; x=1701502835; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=+XAGzqmCrfrRg+GA53ir9QmZ0xE00yciyu9uhHpCHaE=; b=B5HZKRIr1nzpllZNdwXvpMhizbNffpG7Yzy0IkgoP+3z0KflJBT7Y/FbLBfIIOxAQb ZyP1tu8PhNXyK5Kn53L8roF/OPrgLhkYeUz+eiYtPY3ppZitoHIndJMnbajcF+nMM6J5 Rgy56PYxmkVI4ODfebF1auxwExpq53PuomXYxvRHHOg/Xjmmi5eOda0QuL7IP7JeHMzj Bcn1UjWM2RwmI/IN69sEQ3f12nUCPrJ2dWaHDa4n3em1wzepYR695jCV26pEnrXLcyMe 00Y/wxl6DP8Ep62wHpTZ9naVN8rVQXSN7cVrHRtRhvh1D3r0WwkEeA92B4x6JOS2M0XA amEw== X-Gm-Message-State: AOJu0Yz3lenBHsJoYdZfI3yhXwLUf1fweONT27CXJklPTQ1xI8htnhxb e6m6BlxIDiPrv/cApHyy4DHKv0Ua2a/rrVL4quZWTLZFs34= X-Google-Smtp-Source: AGHT+IHOFIJBVV1hH33AcvWnkhBRNutKnYKx4xq/3XrWjm4us2XDQZ4A5Ls64gRAGGuRxE1trJf+FI2vjMoneZpTQTw= X-Received: by 2002:a17:906:c791:b0:9ad:8a96:ad55 with SMTP id cw17-20020a170906c79100b009ad8a96ad55mr8245199ejb.14.1700898034953; Fri, 24 Nov 2023 23:40:34 -0800 (PST) MIME-Version: 1.0 From: Ibrahim Shaame Date: Sat, 25 Nov 2023 10:39:28 +0300 Message-ID: Subject: reporting tree into separate columns To: pgsql-novice@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000002c308f060af52fe8" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000002c308f060af52fe8 Content-Type: text/plain; charset="UTF-8" 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 --0000000000002c308f060af52fe8 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
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>

--0000000000002c308f060af52fe8--