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