Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f295I2x45605 for ; Fri, 9 Mar 2001 00:18:02 -0500 (EST) (envelope-from josh@agliodbs.com) Received: from [209.122.114.86] (HELO agliodbs.com) by davinci.ethosmedia.com (CommuniGate Pro SMTP 3.4.1) with ESMTP id 22587 for pgsql-sql@postgresql.org; Thu, 08 Mar 2001 21:18:01 -0800 Message-ID: <3AA86767.698DDFFF@agliodbs.com> Date: Thu, 08 Mar 2001 21:17:27 -0800 From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions X-Mailer: Mozilla 4.73 [en] (Win98; I) X-Accept-Language: en MIME-Version: 1.0 To: sqllist Subject: List Concatination Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200103/188 X-Sequence-Number: 1841 Folks, 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: CREATE TABLE clients ( client_id SERIAL PRIMARY KEY, client_name VARCHAR(50) ); CREATE TABLE client_contacts ( contact_id SERIAL PRIMARY KEY, client_id REFERENCES clients(client_id), first_name VARCHAR(25), last_name VARCHAR(25) ); CLients: 1 McMurphy Assoc. 2 Ross Construction Contacts 1 1 Jim Henson 2 1 Pat O'Gannet 3 2 Sara Vaugn 3 2 Bill Murray etc. 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 I can figure out how to do this procedurally (using PL/pgSQL and a cursor) but not how to do it declaratively (using only SQL). The reason it's important to do it declaritively is that there are actually two sub-tables with thousands of entries and the procedural approach is rather slow. Suggestions? -Josh Berkus -- ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete information technology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco