From: Vic Cekvenich <maillist@basebeans.com>
To: pgsql-sql@postgresql.org
Subject: Re: Table Pivot
Date: Sun, 16 Feb 2003 12:14:38 -0500
Message-ID: <009001c2d5de$e3792aa0$6e00a8c0@dell150> (raw)
Thanks, I read Rozenshtein's book on Otpimizing
Advanced SQL, and I am reading page 378 of Celkos SQl
4 Smarties.
I understand your case 1.
What is case 2 and 3? Is there a link to find more, or
maybe you can give me a bit more direction please?
Vic
--- Christoph Haller <ch@rodos.fzk.de> wrote:
> >
> > How do you do a table Pivot in PostgreSQL?
> >
> I'll post you this old mail:
>
>
> > > 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
>
> I have an extended example using the 1) method
> implemented in plpgsql.
> Let me know if you want to have a look at it.
>
> Regards, Christoph
>
>
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: maillist@basebeans.com
Subject: Re: Table Pivot
In-Reply-To: <009001c2d5de$e3792aa0$6e00a8c0@dell150>
* 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