not quite a cross tab query...

Have you tried using "group by"?

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

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