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 [EMAIL PROTECTED]
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
