How do I aggregate data from multiple rows into a delimited list?

From: "D(dot) Dante Lorenso" <dante(at)larkspark(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: How do I aggregate data from multiple rows into a delimited list?
Date: 2007-07-01 22:09:38
Message-ID: 46882622.6010709@larkspark.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I want to select several rows of data and have them returned in a single
record with the rows joined by a delimiter. It would be great if this
could be done in a generic way possibly using the GROUP BY like this:

SELECT a.id, a.name, STR_SUM(b.name, ',') AS b_names
FROM a, b
WHERE a.id = b.id
GROUP BY a.id, a.name;

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

The STR_SUM function above would be some aggregate that just joins
records together using concatenation. If the function can't be used as
an aggregate, I suppose I could just use a sub-select:

SELECT a.id, a.name, (
SELECT STR_SUM(b.name, ',')
FROM b
WHERE b.id = a.id) AS b_names
FROM a;

Does my made-up function "STR_SUM" exist in PostgreSQL already? Has
anyone written one they could share? I'm fairly capable with PL/PGSQL
and could write a function to loop through records and concate onto a
string, but before I brute-force this one, I was hoping to find
something more elegant preferred by the community.

Thanks,

-- Dante

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David Fetter 2007-07-01 22:52:25 Re: [pgsql-general] In memory tables/databases
Previous Message Wiebe Cazemier 2007-07-01 22:07:14 Re: Trapping errors from pl/perl (trigger) functions