Tyn Tyn - 1 month ago 8
SQL Question

Concatenate multiple rows in an array with SQL on PostgreSQL

I have a table constructed like this :

oid | identifier | value
1 | 10 | 101
2 | 10 | 102
3 | 20 | 201
4 | 20 | 202
5 | 20 | 203


I'd like to query this table to get a result like this :

identifier | values[]
10 | {101, 102}
20 | {201, 202, 203}


I can't figure a way to do that. Is that possible ? How ?

Thank you very much.

Answer

You have to create an aggregate function, e.g.

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

then

SELECT identifier, array_accum(value) AS values FROM table GROUP BY identifier;

HTH