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

[Dimitri Fontaine]

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.