Re: combine multiple row values in to one row

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(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of Lee Harr
> Sent: Monday, July 06, 2009 5:30 PM
> To: pgsql-general(at)postgresql(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
.now.