Skip site navigation (1) Skip section navigation (2)

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

From: Tim Landscheidt <tim(at)tim-landscheidt(dot)de>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: How to combine many rows into one row (by concatenation?) ?
Date: 2010-06-17 15:43:37
Message-ID: m34oh1r7fa.fsf@passepartout.tim-landscheidt.de (view raw or flat)
Thread:
Lists: pgsql-novice
(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


In response to

pgsql-novice by date

Next:From: Jasen BettsDate: 2010-06-18 09:16:49
Subject: Re: Runnning operating system commands from an SPL
Previous:From: Andreas KretschmerDate: 2010-06-17 15:37:05
Subject: Re: Runnning operating system commands from an SPL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group