Le lundi 02 juillet 2007, D. Dante Lorenso a écrit : > I wanted to select several rows of data and have them returned in a > single record with the rows joined by a delimiter. Turns out this is > very easy to do in PostgreSQL: > > SELECT a.id, a.name, > ARRAY_TO_STRING(ARRAY( > SELECT b.name > FROM b > WHERE b.id = a.id > ORDER BY b.name ASC > ), ',') AS b_names > FROM a > ORDER BY a.id ASC;
In this case I've used this pretty simple custom aggregate:
CREATE AGGREGATE array_acc (
BASETYPE = anyelement,
SFUNC = array_append,
STYPE = anyarray,
INITCOND = '{}'
);
The query would become
SELECT a.id, a.name, array_acc(b.name) as b_names
FROM a LEFT JOIN b USING(id)
GROUP BY a.id, a.name;
The b_names column will have this kind of data: {cheese,milk,eggs}, you could
use array_to_string(array_acc(b.name)) as b_names to obtain what you're
already dealing with.
Hope this helps,
--
dim
signature.asc
Description: This is a digitally signed message part.
