Received: from www-uk-05.digiserve.com ([195.97.231.11]) by mail.postgresql.org (8.11.3/8.11.1) with ESMTP id f2QCYWw93839 for ; Mon, 26 Mar 2001 07:34:32 -0500 (EST) (envelope-from dev@archonet.com) Received: from mainbox.archonet.com (mwynhau.demon.co.uk [193.237.186.96]) by www-uk-05.digiserve.com (8.9.1/8.9.1) with ESMTP id NAA01089; Mon, 26 Mar 2001 13:35:22 +0100 (BST) Received: from client (client.archonet.com [192.168.1.16]) by mainbox.archonet.com (Postfix) with SMTP id 6EC5E2A687; Mon, 26 Mar 2001 13:23:18 +0100 (BST) Message-ID: <006501c0b5ef$897f33c0$1001a8c0@archonet.com> From: "Richard Huxton" To: , References: Subject: Re: Still don't know how to build this string ? Date: Mon, 26 Mar 2001 13:23:15 +0100 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200103/518 X-Sequence-Number: 2189 From: > I have a table with diameters and types. I need to build a comma separated > string. > > typ diam > 01 800 [snip] > > select diam from zylinder where typ='01' > > should produce the string "800,840,870,1120" Try the following as a starting point: CREATE FUNCTION comma_join(text,text) RETURNS text AS ' BEGIN IF $1>\'\' AND $2>\'\' THEN RETURN $1 || \',\' || $2; ELSE RETURN $1 || $2; END IF; END; ' LANGUAGE 'plpgsql'; CREATE AGGREGATE joinall ( sfunc = comma_join, basetype = text, stype = text, initcond = '' ); \d dia Table "dia" Attribute | Type | Modifier -----------+---------+---------- typ | integer | diam | integer | SELECT typ,joinall(diam::text) FROM dia GROUP BY typ; typ | joinall -----+---------------------------- 1 | 800,840,870,1120 2 | 760,800,900,1200,1234,1352 (2 rows) Note the explicit cast of diam into text. - Richard Huxton