Обсуждение: not quite a cross tab query...
Hello pgsql listers, I've got a problem that is similar to, but I don't think identical to, a cross tab query. My data looks like: ID | CAT 1 | A 1 | B 2 | A 2 | C So for each ID there may be many CAT (categories). The client wants it to look like: ID | CATS 1 | A,B 2 | A,C Where each ID is unique in the results, and the CAT values are concatenated with a comma separator. There are about 100 unique CAT values. They only way I can see to do it is programatically with a for loop. But before I do that I wanted to bounce it off the fine minds that inhabit this list. Thanks, Rich -- Richard Greenwood richard.greenwood@gmail.com www.greenwoodmap.com
On Mar 5, 2009, at 11:17 AM, Richard Greenwood wrote: > Hello pgsql listers, > > I've got a problem that is similar to, but I don't think identical to, > a cross tab query. My data looks like: > ID | CAT > 1 | A > 1 | B > 2 | A > 2 | C > So for each ID there may be many CAT (categories). > The client wants it to look like: > ID | CATS > 1 | A,B > 2 | A,C > Where each ID is unique in the results, and the CAT values are > concatenated with a comma separator. > > There are about 100 unique CAT values. They only way I can see to do > it is programatically with a for loop. But before I do that I wanted > to bounce it off the fine minds that inhabit this list. Sounds like a job for array_accum(), which you can find mentioned at http://www.postgresql.org/docs/8.3/interactive/xaggr.html select id, array_to_string(array_accum(cat), ',') from table group by id; There'll be a built-in array_agg() in 8.4, I think. Cheers, Steve
Have you tried using "group by"? > Hello pgsql listers, > > I've got a problem that is similar to, but I don't think identical to, > a cross tab query. My data looks like: > ID | CAT > 1 | A > 1 | B > 2 | A > 2 | C > So for each ID there may be many CAT (categories). > The client wants it to look like: > ID | CATS > 1 | A,B > 2 | A,C > Where each ID is unique in the results, and the CAT values are > concatenated with a comma separator. > > There are about 100 unique CAT values. They only way I can see to do > it is programatically with a for loop. But before I do that I wanted > to bounce it off the fine minds that inhabit this list. > > Thanks, > Rich > > -- > Richard Greenwood > richard.greenwood@gmail.com > www.greenwoodmap.com > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
On Thu, Mar 5, 2009 at 12:27 PM, <darren@ontrenet.com> wrote: > Have you tried using "group by"? Thanks, but that doesn't do it. If I group by ID I loose the CAT, group by CAT I loose the ID, group by bith and that's just the base table. Am I missing something? Regards, Rich >> Hello pgsql listers, >> >> I've got a problem that is similar to, but I don't think identical to, >> a cross tab query. My data looks like: >> ID | CAT >> 1 | A >> 1 | B >> 2 | A >> 2 | C >> So for each ID there may be many CAT (categories). >> The client wants it to look like: >> ID | CATS >> 1 | A,B >> 2 | A,C >> Where each ID is unique in the results, and the CAT values are >> concatenated with a comma separator. >> >> There are about 100 unique CAT values. They only way I can see to do >> it is programatically with a for loop. But before I do that I wanted >> to bounce it off the fine minds that inhabit this list. >> >> Thanks, >> Rich >> >> -- >> Richard Greenwood >> richard.greenwood@gmail.com >> www.greenwoodmap.com >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> > > -- Richard Greenwood richard.greenwood@gmail.com www.greenwoodmap.com
On Thu, Mar 5, 2009 at 12:29 PM, Steve Atkins <steve@blighty.com> wrote: > > On Mar 5, 2009, at 11:17 AM, Richard Greenwood wrote: > >> Hello pgsql listers, >> >> I've got a problem that is similar to, but I don't think identical to, >> a cross tab query. My data looks like: >> ID | CAT >> 1 | A >> 1 | B >> 2 | A >> 2 | C >> So for each ID there may be many CAT (categories). >> The client wants it to look like: >> ID | CATS >> 1 | A,B >> 2 | A,C >> Where each ID is unique in the results, and the CAT values are >> concatenated with a comma separator. >> >> There are about 100 unique CAT values. They only way I can see to do >> it is programatically with a for loop. But before I do that I wanted >> to bounce it off the fine minds that inhabit this list. > > Sounds like a job for array_accum(), which you can find mentioned > at http://www.postgresql.org/docs/8.3/interactive/xaggr.html > > select id, array_to_string(array_accum(cat), ',') from table group by id; > > There'll be a built-in array_agg() in 8.4, I think. > > Cheers, > Steve Thanks, that looks like what I need! Regards, Rich -- Richard Greenwood richard.greenwood@gmail.com www.greenwoodmap.com