X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 8200E47580B for ; Sun, 16 Feb 2003 12:14:37 -0500 (EST) Received: from mail.mailsnare.net (mail.mailsnare.net [207.44.136.42]) by postgresql.org (Postfix) with ESMTP id F00C6475461 for ; Sun, 16 Feb 2003 12:14:36 -0500 (EST) Received: from 127.0.0.1 (localhost [127.0.0.1]) by dummy.domain.name (Postfix) with SMTP id 9605B4640C3 for ; Sun, 16 Feb 2003 17:14:42 +0000 (UTC) Received: from dell150 (pcp01379980pcs.levtwn01.pa.comcast.net [68.81.92.207]) by mail.mailsnare.net (Postfix) with ESMTP id EEB534640C7 for ; Sun, 16 Feb 2003 17:14:41 +0000 (UTC) Message-ID: <009001c2d5de$e3792aa0$6e00a8c0@dell150> From: "Vic Cekvenich" To: Subject: Re: Table Pivot Date: Sun, 16 Feb 2003 12:14:38 -0500 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_008D_01C2D5B4.FA25DC50" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1106 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/361 X-Sequence-Number: 11759 This is a multi-part message in MIME format. ------=_NextPart_000_008D_01C2D5B4.FA25DC50 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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 wrote: > > > > How do you do a table Pivot in PostgreSQL? > > > I'll post you this old mail: >=20 >=20 > > > 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: > > >=20=20 > http://www.mysql.com/articles/wizard/index.html > > > > > > Has anyone tried this on a PostgreSQL database > ? >=20 > 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. >=20 > 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: >=20 > 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. >=20 > 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. >=20 > 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. >=20 > Joe Celko covers these 3 types in "SQL for > Smarties". I will write > a PostgreSQL implementation in a later article. >=20 > -Josh Berkus >=20 > I have an extended example using the 1) method > implemented in plpgsql. > Let me know if you want to have a look at it. >=20 > Regards, Christoph >=20 >=20 ------=_NextPart_000_008D_01C2D5B4.FA25DC50 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Thanks, I read Rozenshtein's book on Otpimizing
Advanced SQL, and I= am=20 reading page 378 of Celkos SQl
4 Smarties.

I understand your case= =20 1.
What is case 2 and 3? Is there a link to find more, or
maybe you c= an=20 give me a bit more direction please?

Vic

--- Christoph Haller= =20 <ch@rodos.fzk.de> wrote:
> >
> = >=20 How do you do a table Pivot in PostgreSQL?
> >
> I'll post y= ou=20 this old mail:
>
>
> > > I saw something that mig= ht=20 somewhat a bit more
> > > flexible solution using SQL. I don't = know=20 if it
> works
> > > in PostgreSQL. I saw it at the MySQL= =20 site.
> > >
> > >   The following is the= =20 URL:
> > >
> http://www.mysql.com/articles/wizard/index.html<= BR>>=20 > >
> > >   Has anyone tried this on a PostgreS= QL=20 database
> ?
>
> Actually, I'm rather annoyed with the= =20 article
> author.   He first claims
> that Joe Celko= =20 reccommends expensive add-on software
> for crosstabs (Joe
> do= es=20 not) and then goes on to use one of Joe's own
> solutions. =20 However,
> given the author's thouroughness otherwise, I'm sure
&g= t;=20 the innaccuracy is
> due to some kind of misunderstanding.
>=20
> There are, in fact, 3 simple SQL-based solutions to
> the=20 crosstab
> problem. Which one you use depends on the shape=20 of
> your data. I am
> not going to cover them in detail = here=20 (I'll save
> that for an article)
> but to sum up:
>
= >=20 1) The SUM(CASE()) statement method, as outlined in
> the article,=20 which
> is good for crosstabs expecting small numbers of
>=20 columns. Or, in the
> case of this article, good for RDBMS whic= h do=20 not
> support subselects.
>
> 2) The LEFT JOIN + Sub-Sel= ect=20 method, which is good
> for crosstabs with
> lots of columns bu= t not=20 that many rows in the
> crosstabbed table.
>
> 3) The=20 "crosstab reference grid" method, which is
> good for large tables>=20 and crosstabs with lots of columns, but requires
> setup and=20 maintainence
> by trigger.
>
> Joe Celko covers these 3 = types=20 in "SQL for
> Smarties".     I will write
>= a=20 PostgreSQL implementation in a later article.
>
> -Josh=20 Berkus
>
> I have an extended example using the 1) method
&= gt;=20 implemented in plpgsql.
> Let me know if you want to have a look at= =20 it.
>
> Regards, Christoph
>
>=20

------=_NextPart_000_008D_01C2D5B4.FA25DC50--