Re: combine multiple row values in to one row

On Tue, Jul 07, 2009 at 01:59:35AM +0430, Lee Harr wrote:
>
> Hi;
>
> I'm looking for a way to do this:
>
>
> # \d tbl
> Table "public.tbl"
> Column | Type | Modifiers
> --------+---------+-----------
> idn | integer |
> code | text |
> # SELECT * FROM tbl;
> idn | code
> -----+------
> 1 | A
> 2 | B
> 2 | C
> 3 | A
> 3 | C
> 3 | E
> (6 rows)
> # select idn, magic() as codes FROM tbl;
> idn | codes
> -----+------
> 1 | A
> 2 | B, C
> 3 | A, C, E
> (3 rows)
>
>
> Right now, I use plpgsql functions, but each time I do it
> I have to rewrite the function to customize it.
>
> Is there a generic way to do this? An aggregate maybe?

The aggregate is called array_agg() and it's in 8.4. You can then
wrap array_to_string() around it and get pretty formatting, as in:

SELECT idn, array_to_string(array_agg(code),', ') AS codes FROM tbl;

If you're not on 8.4 yet, you can create a similar aggregate with
CREATE AGGREGATE.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate