Re: not quite a cross tab query...

On Thu, Mar 5, 2009 at 12:29 PM, Steve Atkins <steve(at)blighty(dot)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(dot)greenwood(at)gmail(dot)com
www.greenwoodmap.com