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

A B <gentosaker@gmail.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°