Received: from mail-relay.trade-india.com (mail-relay.trade-india.com [203.196.129.235]) by postgresql.org (Postfix) with SMTP id BFAB1475425 for ; Tue, 16 Jul 2002 07:30:57 -0400 (EDT) Received: (qmail 3642 invoked from network); 16 Jul 2002 11:22:21 -0000 Received: from system67.trade-india-local.com (192.168.0.67) by infocom-236-129-del.trade-india.com with SMTP; 16 Jul 2002 11:22:21 -0000 Content-Type: text/plain; charset="iso-8859-1" From: "Rajesh Kumar Mallah." Organization: Infocom Network Limited. To: Marcus Claesson , pgsql-sql@postgresql.org Subject: Re: How do I concatenate row-wise instead of column-wise? Date: Tue, 16 Jul 2002 17:04:24 +0530 User-Agent: KMail/1.4.1 References: <3D33D4AA.1020600@angiogenetics.se> In-Reply-To: <3D33D4AA.1020600@angiogenetics.se> MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200207161704.24120.mallah@trade-india.com> X-Archive-Number: 200207/237 X-Sequence-Number: 8615 Hi Marcus, It is simple , you need to write a function and define an aggregate using that function. in case you have already searched for=20=20 the solution and not found here it is from this mailing list only: =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D Date: Tue, 14 May 2002 18:13:09 +0200 From: Mathieu Arnold To: pgsql-sql@postgresql.org Subject: [SQL] aggregate... Message-ID: <1729482965.1021399989@andromede.reaumur.absolight.net> X-Mailer: Mulberry/2.2.1 (Win32) X-wazaaa: True, true MIME-Version: 1.0 Content-Type: text/plain; charset=3Dus-ascii; format=3Dflowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Precedence: bulk Sender: pgsql-sql-owner@postgresql.org Status: RO X-Status: O Hi I have this : CREATE FUNCTION "comma_cat" (text,text) RETURNS text AS ' SELECT CASE WHEN $2 IS NULL OR $2 =3D '''' THEN $1 WHEN $1 IS NULL OR $1 =3D '''' THEN $2 ELSE $1 || '', '' || $2 END ' LANGUAGE 'sql'; CREATE AGGREGATE list ( BASETYPE =3D text, SFUNC =3D comma_cat, STYPE =3D t= ext,=20 INITCOND =3D '' ); I can use it as : select user, list(email) from user join email using (id_user); user | list -------+----------------------------- mat | mat@mat.cc, mat@absolight.fr isa | isa@mat.cc =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D regds On Tuesday 16 July 2002 13:39, you wrote: > I have a table like this: > SELECT * FROM old_tab; > id | descr > ------------------- > 1 | aaa > 1 | aaa > 1 | bbb > 2 | ccc > 2 | bbb > 3 | ddd > 3 | ddd > 3 | eee > 3 | fff > 4 | bbb > etc... > > And I want a new table where the descr is concatenated row-wise like this: > SELECT * FROM new_tab; > id | descr > -------------------------- > 1 | aaa;bbb > 2 | ccc;bbb > 3 | ddd;eee;fff > 4 | bbb > etc... > > This is the closest I get....: > UPDATE new_tab SET descr =3D old_tab.descr||' ; '||old_tab.descr from > old_tab where old_tab.id=3Dnew_tab.id; > UPDATE 4 > SELECT * FROM new_tab ; > id | descr > ----+----------- > 1 | aaa ; aaa > 2 | ccc ; ccc > 3 | ddd ; ddd > 4 | bbb ; bbb > etc... > > Thus, the concatenating operator never works on other rows than the > present. How can I get around that and still stick to the postgresql > syntax? > > Regards > Marcus > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster --=20 Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace.