Re: Generating a cross tab (pivot table)

Christoph, Lud,

> > I saw something that might somewhat a bit more
> > flexible solution using SQL. I don't know if it works
> > in PostgreSQL. I saw it at the MySQL site.
> >
> > The following is the URL:
> > http://www.mysql.com/articles/wizard/index.html
> >
> > Has anyone tried this on a PostgreSQL database ?

Actually, I'm rather annoyed with the article author. He first claims
that Joe Celko reccommends expensive add-on software for crosstabs (Joe
does not) and then goes on to use one of Joe's own solutions. However,
given the author's thouroughness otherwise, I'm sure the innaccuracy is
due to some kind of misunderstanding.

There are, in fact, 3 simple SQL-based solutions to the crosstab
problem. Which one you use depends on the shape of your data. I am
not going to cover them in detail here (I'll save that for an article)
but to sum up:

1) The SUM(CASE()) statement method, as outlined in the article, which
is good for crosstabs expecting small numbers of columns. Or, in the
case of this article, good for RDBMS which do not support subselects.

2) The LEFT JOIN + Sub-Select method, which is good for crosstabs with
lots of columns but not that many rows in the crosstabbed table.

3) The "crosstab reference grid" method, which is good for large tables
and crosstabs with lots of columns, but requires setup and maintainence
by trigger.

Joe Celko covers these 3 types in "SQL for Smarties". I will write
a PostgreSQL implementation in a later article.

-Josh Berkus