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