Re: not quite a cross tab query...

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