Postgresql GROUP_CONCAT equivalent?

Problem

I have a table and I’d like to pull one row per id with field values concatenated.

This is what I have on my table, for example:

TM67 | 4  | 32556
TM67 | 9  | 98200
TM67 | 72 | 22300
TM99 | 2  | 23009
TM99 | 3  | 11200

And here’s what I’d like to produce:

TM67 | 4,9,72 | 32556,98200,22300
TM99 | 2,3    | 23009,11200

I was able to use the aggregate function GROUP CONCAT in MySQL, but that doesn’t appear to be the case here… Is there a PostgreSQL equivalent or another method to accomplish this?

Asked by TwixxyKit

Solution #1

This is even easy with 9.0:

SELECT id, 
       string_agg(some_column, ',')
FROM the_table
GROUP BY id

Answered by a_horse_with_no_name

Solution #2

This is definitely a nice place to start (only version 8.4+):

SELECT id_field, array_agg(value_field1), array_agg(value_field2)
FROM data_table
GROUP BY id_field

array agg returns an array, which you can CAST to text and edit if necessary (see clarifications, below).

Before version 8.4, you had to specify it yourself before using it:

CREATE AGGREGATE array_agg (anyelement)
(
    sfunc = array_append,
    stype = anyarray,
    initcond = '{}'
);

(as quoted in the PostgreSQL docs)

Clarifications:

Answered by Matthew Wood

Solution #3

SELECT array_to_string(array(SELECT a FROM b),', ');

That’ll suffice.

Answered by genobis

Solution #4

Try like this:

select field1, array_to_string(array_agg(field2), ',')
from table1
group by field1;

Answered by max_spy

Solution #5

Assuming that the table your_table has three columns (name, id, value), the query is this one:

  select name, 
         array_to_string(array_agg(id), ','), 
         array_to_string(array_agg(value), ',')
    from your_table
group by name
order by name
;

"TM67"  "4,9,72"    "32556,98200,22300"
"TM99"  "2,3"       "23009,11200"

KI

Answered by Kérdezösködő Indián

Post is based on https://stackoverflow.com/questions/2560946/postgresql-group-concat-equivalent