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.