Re: Cross-Tab queries in postgres?

Martijn van Oosterhout wrote:
> I know they're not supported and that they should be done in the
> presentation end of the software. However, I have a case where I need to use
> the result as the input to another query. So I'm reading the output, doing
> the cross-tab and copying the result back into the database.
>

Funny you should ask about this today. See my post to patches from
yesterday:

http://archives.postgresql.org/pgsql-patches/2002-07/msg00247.php

specifically:

crosstabN(text sql)
    - returns a set of row_name plus N category value columns
    - crosstab2(), crosstab3(), and crosstab4() are defined for you,
      but you can create additional crosstab functions per directions
      in the README.

crosstabN example usage
test=# select * from ct where rowclass = 'group1' and (attribute =
'att2' or attribute = 'att3');
  id | rowclass | rowid | attribute | value
----+----------+-------+-----------+-------
   2 | group1   | test1 | att2      | val2
   3 | group1   | test1 | att3      | val3
   6 | group1   | test2 | att2      | val6
   7 | group1   | test2 | att3      | val7
(4 rows)

select * from crosstab3(
    'select rowid, attribute, value
     from ct
     where rowclass = ''group1''
     and (attribute = ''att2'' or attribute = ''att3'') order by 1,2;');

   row_name | category_1 | category_2 | category_3
----------+------------+------------+------------
   test1    | val2       | val3       |
   test2    | val6       | val7       |
(2 rows)

and a follow-up at:
http://archives.postgresql.org/pgsql-patches/2002-07/msg00250.php

If you want to try it get an update from cvs and apply the three patches
(well, one is just a doc patch) from the *second* post.

This is not exactly what you have described, but pretty close. Take a
look at the README. I think it currently is not as flexible as your
example would need, but could be reasonably easily modified.

> Anyway, it doesn't seem to hard to implement so I was wondering if any other
> database systems actually implement it. Mostly I'm interested in what syntax
> they use to indicate such a query. (I presume it's not in the standard or
> it'd be there already).

I haven't seen this except in MS Access. I don't think you can directly
produce a crosstab in MS SQL Server or Oracle, although in Oracle you
can build your own table function.