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 1r7DOg-004adu-Q9 for pgsql-novice@arkaria.postgresql.org; Sun, 26 Nov 2023 11:33: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 1r7DOf-0082yC-Dw for pgsql-novice@arkaria.postgresql.org; Sun, 26 Nov 2023 11:33: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 1r7DOe-0082y0-T0 for pgsql-novice@lists.postgresql.org; Sun, 26 Nov 2023 11:33:57 +0000 Received: from mail-pf1-x42b.google.com ([2607:f8b0:4864:20::42b]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1r7DOc-007R9K-Dd for pgsql-novice@lists.postgresql.org; Sun, 26 Nov 2023 11:33:55 +0000 Received: by mail-pf1-x42b.google.com with SMTP id d2e1a72fcca58-6cbe716b511so2166005b3a.3 for ; Sun, 26 Nov 2023 03:33:54 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1700998433; x=1701603233; 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=i/V4MJ/+VaqpI6g2Ob/UZFYmJYb7mxc5bLp8PoybxGw=; b=VZP2kwhcPNCi9m6O0t3FHfDM5YU5oga02A6bN3zPYbX0eTyKdtULHSxif2T3MGyCgu a+g0BSqqBZvrZ9X46MJg0yhlaIOop/GlZiub/zdHnoc75pFeYBY+gmHzGAy5uONhDyDv 5zef9+t2TvcSRLDdmEU/XWaq63kQnSXo/Ie8IXphTkK5kUhgnDa9Y4Jplmp1yMhuJ/L4 axaMeNdQvuoiWKf+ZQm2RaUElpPTRn+ZtHXcMm+EMapJAmN0IyNiT6NnLQMeV3lRxubH WYvMxhNeLHcd688sqa0kke2Vk852hthcNOCaqapue7oeCzmasXlTBvYw1zsgjslu/7Ks UGfQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1700998433; x=1701603233; 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=i/V4MJ/+VaqpI6g2Ob/UZFYmJYb7mxc5bLp8PoybxGw=; b=vQR04Rh7kDp98y0EDlYfIHfzZyxExJeh9O4XEUVafHhCpPhLiK0qGKoDvvysT3UHFk eTTPxPFlY98gYp2UYLvUGGM/Qb4mEUdsLkKdjkQNkaAiA8d65Wbbp54ZUWs39pALDqdB Tq1/hciSMNZbaRMAJ9eaYB5DefuBDwOIhkVO0uD1Tr32U4dt4U39Fh8N5ELjIq7YJKYc 165t8R9N+d5o9aYwFI0R7JJd29S/f2GRQycpIQQgzHBkqZu42we/V9BtCQpMUNxYsgwA e5VD7qpiNXkD0P7rbTFhyg7viLzYF43BXkFayoBLPY/S4pQjXOWPBc0j1rzijZW3wRj6 VbKg== X-Gm-Message-State: AOJu0Yx40qhXQNcKHcskUom7vxEqMcWSq3sqAbBxe8MzNTTDsSTAmjq4 tIvpnm5a+8aNUWvQmSBOofzver28Vo8RFO6ihHY= X-Google-Smtp-Source: AGHT+IEXwYRxo5wnINhTles5qBBSSe9ipzcUoHLOw4/hmLT5T9AIFYksgbI5xq5tkwNZyxrtp8/CQCCQ0pr8wLTBtEw= X-Received: by 2002:a05:6a21:789a:b0:18c:3260:e20f with SMTP id bf26-20020a056a21789a00b0018c3260e20fmr4339908pzc.33.1700998433353; Sun, 26 Nov 2023 03:33:53 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Tomek Date: Sun, 26 Nov 2023 12:33:41 +0100 Message-ID: Subject: Re: reporting tree into separate columns To: Ibrahim Shaame Cc: pgsql-novice@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000623617060b0c8f8b" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000623617060b0c8f8b Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Now you can use regexp_split_to_array Look like it works: select (regexp_split_to_array('Asia Khamis Haji - Ishak Makame Haji - Ibrahim Ishak Makame', '-'))[1] , (regexp_split_to_array('Asia Khamis Haji - Ishak Makame Haji - Ibrahim Ishak Makame', '-'))[2] , (regexp_split_to_array('Asia Khamis Haji - Ishak Makame Haji - Ibrahim Ishak Makame', '-'))[3] , (regexp_split_to_array('Asia Khamis Haji - Ishak Makame Haji - Ibrahim Ishak Makame', '-'))[4] Or from the beginning in your CTE insert values to proper place in array instead of building concatenated string separated with ' - ' Regards Tomek (szaman) sob., 25 lis 2023 o 08:40 Ibrahim Shaame napisa=C5=82(a= ): > 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 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 > --000000000000623617060b0c8f8b Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Now you can use=C2=A0regexp_split_to_array=C2=A0
Look like it works:<= br>select (regexp_split_to_array('Asia Khamis Haji - Ishak Makame Haji = - Ibrahim Ishak Makame', '-'))[1]
, (regexp_split_to_array(&= #39;Asia Khamis Haji - Ishak Makame Haji - Ibrahim Ishak Makame', '= -'))[2]
, (regexp_split_to_array('Asia Khamis Haji - Ishak Makam= e Haji - Ibrahim Ishak Makame', '-'))[3]
, (regexp_split_to_= array('Asia Khamis Haji - Ishak Makame Haji - Ibrahim Ishak Makame'= , '-'))[4]

Or from the beginning=C2=A0in your CTE insert values to proper pl= ace in array instead of building concatenated string separated with ' -= '
Regard= s Tomek
(= szaman)

sob., 25 lis 2023 o 08:40=C2=A0Ibrahim Shaame <ishaame@gmail.com> napisa=C5=82(a):
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>

--000000000000623617060b0c8f8b--