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

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

D. Dante Lorenso wrote:
> I want to select several rows of data and have them returned in a
> single record with the rows joined by a delimiter.


Review the User Comments at

"http://www.postgresql.org/docs/8.0/interactive/sql-createaggregate.html"

for some ideas.

D. Dante Lorenso wrote:
> I want to select several rows of data and have them returned in a single
> record with the rows joined by a delimiter.

I have something like that here:

http://www.designmagick.com/article/38

--
Postgresql & php tutorials
http://www.designmagick.com/

Berend Tober wrote:
> D. Dante Lorenso wrote:
>> I want to select several rows of data and have them returned in a
>> single record with the rows joined by a delimiter.
>
> Review the User Comments at
> "http://www.postgresql.org/docs/8.0/interactive/sql-createaggregate.html"
> for some ideas.

I found a better solution using ARRAY_TO_STRING and ARRAY.  My question
email was originally blocked because I sent it to the list from the
wrong email address.  Unfortunately it was later unfrozen and sent on to
the list (sorry) because I did more searching and had solved the problem
on my own:

http://archives.postgresql.org/pgsql-general/2007-07/msg00075.php

Thanks for all your help, though!

-- Dante

On Jul 1, 3:09 pm, d...@larkspark.com ("D. Dante Lorenso") wrote:
> 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:

I recently wrote this to address the same need. I practically copied
it straight from the "CREATE AGGREGATE" documentation, so look there
for more information. It will create a "text_accumulate" aggregate
function that does what you need:

CREATE FUNCTION text_append(text,text) RETURNS text
  AS 'select $1 || '','' || $2;'
  LANGUAGE SQL
  IMMUTABLE
  RETURNS NULL ON NULL INPUT;

CREATE AGGREGATE text_accumulate(
  BASETYPE = text,
  SFUNC = text_append,
  STYPE = text
);


Geoff