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
