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 1r7Jjc-004yDP-VP for pgsql-novice@arkaria.postgresql.org; Sun, 26 Nov 2023 18:20:01 +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 1r7JjZ-0093Eu-86 for pgsql-novice@arkaria.postgresql.org; Sun, 26 Nov 2023 18:19:57 +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 1r7JjY-0093El-UP for pgsql-novice@lists.postgresql.org; Sun, 26 Nov 2023 18:19:56 +0000 Received: from mail-lf1-x129.google.com ([2a00:1450:4864:20::129]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1r7JjS-008eWL-N2 for pgsql-novice@lists.postgresql.org; Sun, 26 Nov 2023 18:19:56 +0000 Received: by mail-lf1-x129.google.com with SMTP id 2adb3069b0e04-507c5249d55so5358131e87.3 for ; Sun, 26 Nov 2023 10:19:50 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1701022790; x=1701627590; 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=o8+bxPagBTMdw+JamU67Ry/mri/Nk5Kg9Kiswh93ED0=; b=hK6sKFgZZmqf7UHtgV+a+VPBHZsHSTuPkYJsGCckfMTjc/EU8aDsx7IhWTLjfsKl25 xFKEq+MMpw0qiqMPUD4Rr0vGNEQfYDAKLN0Nel99WxPSP6+ndGiNG7WOvJLpwR8W/tcu 0CvsoUS5QzTyRPQs7DPcCYJhLVFaFbx6moYT5MQdblxOwmSSk1CW15lxAjESkNFbmPZY z1jLvGsbMZt1As2LWoCLXizj4RZXGyNYJMEkcQvb7G3IhrH46WceGQ5JhQtYnYLZB/b+ yiaaIQLel76fisdpLSduI1Y+40ndLMBh9UmY6vo8HWLgZougH8EJM/vc+3oDWxUSiL0p ROig== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1701022790; x=1701627590; 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=o8+bxPagBTMdw+JamU67Ry/mri/Nk5Kg9Kiswh93ED0=; b=mggDU051xCUP3w13xVFYS5VZa4p2GB87VIQIqeySBCCoeXYlGS5Dl/Ch2tZ29gE1Q3 Wv6uwP64gpcLWGBt5POj4iJQsPxN5DsEANkxjbhSkuLJXpXGZtnE3X6wh1ekhtHpp4Jb 1dtMdJbWIzXtkhRhrcQzQD7q5dEdGp74cckDCjwexI1p2A2mo2iG+EXVHRoc2yaBZkHH oSaYLfLimpLYWhORzTxZow4IaTSGjaUWRXBiHAeWjwrbZo1eHzZS7rs+n22dvVSnrDy8 WYQBdvaImTbabeF09YGCuBnyU5+ywyMUlrBOWO2mXVx7JvrDpX3LM6ooxU+ucir+Szba kfqA== X-Gm-Message-State: AOJu0YzsDx+jQkaZ0JQMCPPnmIeu9uoBVRjxYLlGJRURysTB0Kc3rNhz E21BBwLf8LNBiw5xaw69UY+vh9xwnbaoypsF5lM= X-Google-Smtp-Source: AGHT+IH6Vn+NJCagGlhf1THEyokHq7Jhw3bqhGhpKC2PaLdRneINav9vMWW7JqMmawB8cDx5qqb0Cli+7HxVVXHW3Ds= X-Received: by 2002:a05:6512:909:b0:50a:aa80:546e with SMTP id e9-20020a056512090900b0050aaa80546emr5699741lft.59.1701022789415; Sun, 26 Nov 2023 10:19:49 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Ibrahim Shaame Date: Sun, 26 Nov 2023 21:18:42 +0300 Message-ID: Subject: Re: reporting tree into separate columns To: Tomek Cc: pgsql-novice@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000001e39f8060b123bb3" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000001e39f8060b123bb3 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Thanks for the response : ...... insert values to proper place in array instead of building concatenated string separated with ' - ' Any proposal for it. Because there I have not succeeded. On Sun, Nov 26, 2023 at 2:33=E2=80=AFPM Tomek wrote= : > 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 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 >> > --0000000000001e39f8060b123bb3 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Thanks for the response :
...... insert values to proper place in array instead of buildi= ng concatenated string separated with ' - '
Any proposal = for it. Because there I have not succeeded.




On Sun, Nov 26, 2023 at 2:33=E2=80=AFPM Tomek <= tomekphotos@gmail.com> wrot= e:
Now you can use=C2=A0regexp_split_= to_array=C2=A0
Look like it works:
select (regexp_split_to_array('= ;Asia Khamis Haji - Ishak Makame Haji - Ibrahim Ishak Makame', '-&#= 39;))[1]
, (regexp_split_to_array('Asia Khamis Haji - Ishak Makame H= aji - Ibrahim Ishak Makame', '-'))[2]
, (regexp_split_to_arr= ay('Asia Khamis Haji - Ishak Makame Haji - Ibrahim Ishak Makame', &= #39;-'))[3]
, (regexp_split_to_array('Asia Khamis Haji - Ishak M= akame Haji - Ibrahim Ishak Makame', '-'))[4]

= Or from the beginning=C2=A0in your CTE insert values to proper place in arr= ay instead of building concatenated string separated with ' - '

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

--0000000000001e39f8060b123bb3--