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

A B <gentosaker(at)gmail(dot)com> wrote:

> Hi.
>
> 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?

Sure: (with 8.4)

est=*# select * from test;
id | val
----+-----
1 | 10
1 | 20
1 | 30
2 | 100
2 | 200
(5 Zeilen)

Zeit: 0,223 ms
test=*# select id, array_to_string(array_agg(val),' ') from test group by id order by id;
id | array_to_string
----+-----------------
1 | 10 20 30
2 | 100 200
(2 Zeilen)

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°