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