Обсуждение: How-To: Aggregate data from multiple rows into a delimited list.

This is not a question, but a solution.  I just wanted to share this
with others on the list in case it saves you a few hours of searching...

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;

Sample data would look like this:

[table a]
  id | name
----+------
1   | one
2   | two
3   | three
4   | four

[table b]
  id | name
----+------
1   | pizza
1   | hot dog
2   | gorilla
2   | monkey
3   | apple
4   | cheese
4   | milk
4   | eggs

And the result would look like this:

  id | name  | b_names
----+-------+---------
1   | one   | pizza,hot dog
2   | two   | gorilla,monkey
3   | three | apple
4   | four  | cheese,milk,eggs

This is an easy way to return attributes of a record from another table
without having to issue multiple queries or deal with multiple result
records.

Enjoy!

-- Dante

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

> 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;
>

All variants are possible. Variant with array_to_string is faster and
doesn't need own aggregate function. And you can easy remove
duplicities.

SELECT a.id, a.name,
      ARRAY_TO_STRING(ARRAY(
        SELECT DISTINCT b.name
        FROM b
        WHERE b.id = a.id
        ORDER BY b.name ASC
      ), ',') AS b_names
    FROM a
    ORDER BY a.id ASC;

regards
Pavel