Received: from www-uk-05.digiserve.com ([195.172.23.63]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f29BYTx47319 for ; Fri, 9 Mar 2001 06:34:29 -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 LAA20604; Fri, 9 Mar 2001 11:35:38 GMT Received: from archonet.com (ibook.archonet.com [192.168.1.32]) by mainbox.archonet.com (Postfix) with ESMTP id 516BC2A479; Fri, 9 Mar 2001 11:16:18 +0000 (GMT) Message-ID: <3AA8BB73.5647B248@archonet.com> Date: Fri, 09 Mar 2001 11:16:04 +0000 From: Richard Huxton X-Mailer: Mozilla 4.7C-CCK-MCD {C-UDP; EBM-APPLE} (Macintosh; I; PPC) X-Accept-Language: en MIME-Version: 1.0 To: josh@agliodbs.com Cc: sqllist Subject: Re: List Concatination References: <3AA86767.698DDFFF@agliodbs.com> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200103/189 X-Sequence-Number: 1842 Josh Berkus wrote: > I have an interesting problem. For purpose of presentation to users, > I'd like to concatinate a list of VARCHAR values from a subtable. To > simplify my actual situation: > > What I'd like to be able to do is present a list of clients and their > comma-seperated contacts in paragraph form, hence: > > Client Contacts > McMurphy Assoc. Jim Henson, Pat O'Gannet, Kelley Moore > > Ross Construction Sara Vaugn, Bill Murray, Peter Frump, > Siskel Ebert > Well, basically you can use a standard join, order it and eliminate duplicate client names in the application. That's the "proper" way. But - if you don't care about the order of contacts you can define an aggregate function: create aggregate catenate(sfunc1=textcat, basetype=text, stype1=text, initcond1=''); Then group by client and catenate(firstname || ' ' || lastname) You'll want to read the CREATE AGGREGATE page in the reference manual, replace textcat with your own routine that adds a comma and you'll need a finalisation routine to strip the final trailing comma. Note that this is probably not a good idea - the ordering of the contacts will not be well-defined. When I asked about this Tom Lane was quite surprised that it worked, so no guarantees about long-term suitability. - Richard Huxton