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?
Re: How do I aggregate data from multiple rows into a delimited list? Re: How do I aggregate data from multiple rows into a delimited list? |
Список | 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 по дате отправления: