From: | <terry(at)ashtonwoodshomes(dot)com> |
---|---|
To: | "Postgresql Sql Group (E-mail)" <pgsql-sql(at)postgresql(dot)org> |
Subject: | Return relation table data in a single value CSV |
Date: | 2004-02-17 20:05:12 |
Message-ID: | 007701c3f591$5acc3020$2766f30a@development.greatgulfhomes.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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(at)greatgulfhomes(dot)com
Fax: (416) 441-9085
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2004-02-17 20:26:41 | Re: SQL query seach + rearranging results |
Previous Message | Richard Huxton | 2004-02-17 19:26:35 | Re: FW: Function |