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> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: terry@ashtonwoodshomes.com
Subject: Re: Return relation table data in a single value CSV
In-Reply-To: <007701c3f591$5acc3020$2766f30a@development.greatgulfhomes.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox