Re: How-To: Aggregate data from multiple rows into a delimited list.

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