X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 16A4347580B for ; Sun, 16 Feb 2003 12:12:50 -0500 (EST) Received: from mail.mailsnare.net (mail.mailsnare.net [207.44.136.42]) by postgresql.org (Postfix) with ESMTP id 2FC7C475461 for ; Sun, 16 Feb 2003 12:12:48 -0500 (EST) Received: from 127.0.0.1 (localhost [127.0.0.1]) by dummy.domain.name (Postfix) with SMTP id 82F2D46408E for ; Sun, 16 Feb 2003 17:12:53 +0000 (UTC) Received: from dell150 (pcp01379980pcs.levtwn01.pa.comcast.net [68.81.92.207]) by mail.mailsnare.net (Postfix) with ESMTP id 1A9454640C4 for ; Sun, 16 Feb 2003 17:12:53 +0000 (UTC) Message-ID: <007a01c2d5de$a2958880$6e00a8c0@dell150> From: "Vic Cekvenich" To: Subject: Re: Table Pivot Date: Sun, 16 Feb 2003 12:12:49 -0500 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0077_01C2D5B4.B94FF5D0" 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/360 X-Sequence-Number: 11758 This is a multi-part message in MIME format. ------=_NextPart_000_0077_01C2D5B4.B94FF5D0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable > > 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 >=20 > If you're using 7.3.x, and don't mind a function > based (vs pure sql=20 > based) approach, take a look at crosstab() in > contrib/tablefunc. I looked but could not find. Where Can I find this? >=20 > It has a limitation in that the data source query > must provide for=20 > "missing" rows. In other words, if your query > produces: >=20 > id1 cat1 val > id1 cat2 val > id2 cat1 val > id2 cat2 val > id2 cat3 val >=20 > and you specify 3 catagory columns to the crosstab > function, then=20 > crosstab() will not give the result you're probably > expecting. I=20 > typically work around that by doing a sub-select > that is the=20 > cross-product of (distinct id) and (distinct cat), > and then left joining=20 > that to the actual data. That will produce somthing > like: >=20 > id1 cat1 val > id1 cat2 val > id1 cat3 NULL > id2 cat1 val > id2 cat2 val > id2 cat3 val >=20 > For large numbers of rows and columns (at least with > my data) I've found=20 > that crosstab() provides a significant performance > boost. Cool, if I can find a link to compiling the Crosstab function. tia, .V >=20 > HTH, >=20 > Joe >=20 >=20 > ---------------------------(end of > broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? >=20 > http://www.postgresql.org/users-lounge/docs/faq.html =3D=3D=3D=3D=3D ------=_NextPart_000_0077_01C2D5B4.B94FF5D0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

> > I have an extended example u= sing the=20 1) method
> implemented in plpgsql.
> > Let me know if you w= ant=20 to have a look at it.
> >
>
> If you're using 7.3.x,= and=20 don't mind a function
> based (vs pure sql
> based) approach, = take=20 a look at crosstab() in
> contrib/tablefunc.

I looked but coul= d not=20 find.
Where Can I find this?

>
> It has a limitatio= n in=20 that the data source query
> must provide for
> "missing" rows= . In=20 other words, if your query
> produces:
>
>=20 id1 cat1 val
> id1 cat2 val
> id2 cat1 val
>=20 id2 cat2 val
> id2 cat3 val
>
> and you specify 3 catago= ry=20 columns to the crosstab
> function, then
> crosstab() will not= give=20 the result you're probably
> expecting. I
> typically work aro= und=20 that by doing a sub-select
> that is the
> cross-product of=20 (distinct id) and (distinct cat),
> and then left joining
> th= at to=20 the actual data. That will produce somthing
> like:
>
>= =20 id1 cat1 val
> id1 cat2 val
> id1 cat3 NULL
>=20 id2 cat1 val
> id2 cat2 val
> id2 cat3 val
>
> For= =20 large numbers of rows and columns (at least with
> my data) I've foun= d=20
> that crosstab() provides a significant performance
>=20 boost.

Cool, if I can find a link to compiling the=20 Crosstab
function.
tia,
.V

>
> HTH,
>
&= gt;=20 Joe
>
>
> ---------------------------(end of
>=20 broadcast)---------------------------
> TIP 5: Have you checked our= =20 extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html=

=3D=3D=3D=3D=3D

------=_NextPart_000_0077_01C2D5B4.B94FF5D0--