Обсуждение: combine multiple row values in to one row

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?


Thanks for any help.


_________________________________________________________________
Invite your mail contacts to join your friends list with Windows Live Spaces. It's easy!
http://spaces.live.com/spacesapi.aspx?wx_action=create&wx_url=/friends.aspx&mkt=en-us

On Tue, 7 Jul 2009 01:59:35 +0430
Lee Harr <missive@hotmail.com> wrote:

>
> Hi;
>
> I'm looking for a way to do this:

> # 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?

array_accum
http://www.postgresql.org/docs/8.2/static/xaggr.html
?


--
Ivan Sergio Borgonovo
http://www.webthatworks.it

Try this.

select    idn,
        array_to_string(array(select code from tbl t2 where
t2.idn = t1.idn order by code), ', ') as codes
from        tbl t1
group by    idn
order by    idn


Matthew Hartman
Programmer/Analyst
Information Management, ICP
Kingston General Hospital
(613) 549-6666 x4294

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Lee Harr
> Sent: Monday, July 06, 2009 5:30 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] combine multiple row values in to one row
>
>
> 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?
>
>
> Thanks for any help.
>
>
> _________________________________________________________________
> Invite your mail contacts to join your friends list with Windows Live
> Spaces. It's easy!
>
http://spaces.live.com/spacesapi.aspx?wx_action=create&wx_url=/friends.a
sp
> x&mkt=en-us
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
.now.

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@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

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

On Tue, Jul 07, 2009 at 08:40:06AM -0700, David Fetter wrote:
> On Tue, Jul 07, 2009 at 01:59:35AM +0430, Lee Harr wrote:
> >
> > 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;

Oops.  That should read:

SELECT
    idn,
    array_to_string(array_agg(code),', ') AS codes
FROM tbl
GROUP BY idn; /* gotta group by :) */

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

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