Re: How to combine many rows into one row (by concatenation?) ?

(anonymous) wrote:

> The table is   table foo( id integer, x integer);  and let the data be
> id    x
> =====
> 1     10
> 1     20
> 2     20
> 3     30
> 3     10

> Now I would like to get the data in  a format like this

> 1 , 10 20
> 2 , 20
> 3 , 10 30

> where id is the first field, and the second field is the x values sorted.

> Is there a simple way to do this?

You can do something like this:

| tim=# SELECT ID, array_to_string(array_agg(x), ' ') FROM (SELECT * FROM foo ORDER BY id, x) AS s GROUP BY ID;
|  id | array_to_string
| ----+-----------------
|   1 | 10 20
|   2 | 20
|   3 | 10 30
| (3 Zeilen)

| tim=#

... *but*: Strictly speaking, the order of the elements in
the aggregate isn't defined and could change.

Tim