Message-ID: <20030216165111.64562.qmail@web40605.mail.yahoo.com>
Date: Sun, 16 Feb 2003 08:51:11 -0800 (PST)
From: "V. Cekvenich" <vc@basebeans.com>
Reply-To: vc@basebeans.com
Subject: Fwd: Re: Table Pivot
To: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii


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


=====