Re: Table Pivot

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(at)rodos(dot)fzk(dot)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
>
>