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

Поиск
Список
Период
Сортировка
От D. Dante Lorenso
Тема How do I aggregate data from multiple rows into a delimited list?
Дата
Msg-id 46882622.6010709@larkspark.com
обсуждение исходный текст
Ответы Re: How do I aggregate data from multiple rows into a delimited list?  (Berend Tober <btober@ct.metrocast.net>)
Re: How do I aggregate data from multiple rows into a delimited list?  (Chris <dmagick@gmail.com>)
Re: How do I aggregate data from multiple rows into a delimited list?  (Gunzler <geoff.kloess@gmail.com>)
Список 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



В списке pgsql-general по дате отправления:

Предыдущее
От: Richard Huxton
Дата:
Сообщение: Re: Stored Procedure: Copy table from; path = text variable
Следующее
От: Bjorn Boulder
Дата:
Сообщение: [pgsql.general] Problem connecting PostgreSQL 8.2 to Rails 1.2