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

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?

I should have added that the "concatenation" of the data  to 10 20
or 10 30  or something is not really important how it is done, the
main point is to get only two fields in each row that is returned.

If the result is concatenated would be easiest, but perhaps there are
other ways to combine all the x values?

2010/6/17 A B <gentosaker@gmail.com>:
> 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?
>

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°

(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