Received: from wallace.ece.rice.edu (root@wallace.ece.rice.edu [128.42.12.154]) by hub.org (8.9.3/8.9.3) with ESMTP id LAA98550 for ; Sun, 15 Aug 1999 11:36:42 -0400 (EDT) (envelope-from reedstrm@wallace.ece.rice.edu) Received: by wallace.ece.rice.edu via sendmail from stdin id (Debian Smail3.2.0.102) for pgsql-sql@postgreSQL.org; Sun, 15 Aug 1999 10:36:26 -0500 (CDT) Date: Sun, 15 Aug 1999 10:36:26 -0500 From: "Ross J. Reedstrom" To: Herouth Maoz Cc: Mike Field , pgsql-sql@postgreSQL.org Subject: Re: [SQL] Multiple values for a field Message-ID: <19990815103626.A19124@wallace.ece.rice.edu> References: <008701bee59e$0e951f80$0200a8c0@mike> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Mailer: Mutt 0.95.3i In-Reply-To: ; from Herouth Maoz on Sun, Aug 15, 1999 at 04:26:31PM +0300 On Sun, Aug 15, 1999 at 04:26:31PM +0300, Herouth Maoz wrote: > > An alternative may be to create an aggregate function that will give you > the comma-separated list you desired upon request: > > CREATE AGGREGATE textcat_all > ( > basetype = text, > sfunc1 = textcat, > stype1 = text > ); > > Then you can do something like: > > SELECT da.docid, textcat_all( a.Author_name || ',' ) > FROM docs_authors da, authors a > WHERE da.authorid = a.id > GROUP BY da.docid; > > This will return a comma-separated list of author names for each doc id, > assuming the actual author name is in a separate table. There is an extra > comma at the end, but I don't suppose that's too much of a problem. > And can be solved as so: SELECT da.docid, rtrim(textcat_all( a.Author_name || ',' ), ',') ... Excellent tip, Herouth. This one goes in the 'keepers' bag. Ross -- Ross J. Reedstrom, Ph.D., NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005