Fwd: Re: Table Pivot

--- "V. Cekvenich" <vc(at)basebeans(dot)com> wrote:
> Date: Sun, 16 Feb 2003 08:50:19 -0800 (PST)
> From: "V. Cekvenich" <vc(at)basebeans(dot)com>
> Subject: Re: [SQL] Table Pivot
> To: Christoph Haller <ch(at)rodos(dot)fzk(dot)de>
>
> 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
> >
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to
> majordomo(at)postgresql(dot)org
>
>
> =====
>
>

=====