From: Josh Berkus <josh@agliodbs.com>
To: Christoph Haller <ch@rodos.fzk.de>
To: pgsql-sql@postgresql.org
Cc: lud_nowhere_man@yahoo.com
Subject: Re: Generating a cross tab (pivot table)
Date: Mon, 11 Nov 2002 10:27:59 -0800
Message-ID: <web-1821228@davinci.ethosmedia.com> (raw)
In-Reply-To: <3DCFCFED.1F81C503@rodos.fzk.de>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com, ch@rodos.fzk.de, lud_nowhere_man@yahoo.com
Subject: Re: Generating a cross tab (pivot table)
In-Reply-To: <web-1821228@davinci.ethosmedia.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox