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