I have a table and I'd like to pull one row per id with field values concatenated.
In my table, for example, I have this:
TM67 | 4 | 32556
TM67 | 9 | 98200
TM67 | 72 | 22300
TM99 | 2 | 23009
TM99 | 3 | 11200
And I'd like to output:
TM67 | 4,9,72 | 32556,98200,22300
TM99 | 2,3 | 23009,11200
In MySQL I was able to use the aggregate function GROUP_CONCAT
, but that doesn't seem to work here... Is there an equivalent for PostgreSQL, or another way to accomplish this?
In Postgres 8.4 or later, this is probably a good starting point:
SELECT id_field, array_agg(value_field1), array_agg(value_field2)
FROM data_table
GROUP BY id_field
The array_agg
function returns an array, but you can CAST that to text and edit as needed (see clarifications, below).
Prior to version 8.4, you have to define the method yourself prior to use:
CREATE AGGREGATE array_agg (anyelement)
(
sfunc = array_append,
stype = anyarray,
initcond = '{}'
);
(paraphrased from the PostgreSQL documentation)
Clarifications:
array_to_string()
or string_agg()
(the group_concat
analogous function added in 9.0) quote strings with embedded commas, resulting in an incorrect number of elements in the resulting list.string_agg()
function does NOT cast the inner results to TEXT first. So string_agg(value_field)
would generate an error if value_field
is an integer. string_agg(value_field::text)
would be required. The array_agg()
method requires only one cast after the aggregation (rather than a cast per value).