From: <terry@ashtonwoodshomes.com>
To: "Postgresql Sql Group (E-mail)" <pgsql-sql@postgresql.org>
Subject: Return relation table data in a single value CSV
Date: Tue, 17 Feb 2004 15:05:12 -0500
Message-ID: <007701c3f591$5acc3020$2766f30a@development.greatgulfhomes.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Importance: Normal

I should probably be punished for even asking this question, but a
simplified version of what I want is this...

I have 2 tables:
floorplans
floorplan_id | description
--------------------------
2240         | test floorplan

and a table elevations
floorplan_id | elevation
------------------------
2240         | A
2240         | B
2240         | C

I want to perform a query that returns this result set:
baseplan_id | elevations
2240        | A,B,C


The real query is, of course, *much* more complex then that, as there are
many more fields in floorplans, and floorplans is joined to other tables.
Currently I return my floorplan information, then perform a second query to
get the elevation records, and loop over the second query to compile the
comma separated list of elevations.

I have tried subselects concatenated with basically || ',' || where each
subselect does an OFFSET X LIMIT 1, and the ',' is wrapped with a case
statement to hide the comma if there are no further elevations.  It gets
very messy very fast as and further I end up hard coding the max number of
elevations.

Any ideas?

Terry Fielder
Manager Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
terry@greatgulfhomes.com
Fax: (416) 441-9085