From: Vic Cekvenich <maillist@basebeans.com>
To: pgsql-sql@postgresql.org
Subject: Re: Table Pivot
Date: Sun, 16 Feb 2003 12:12:49 -0500
Message-ID: <007a01c2d5de$a2958880$6e00a8c0@dell150> (raw)
> > I have an extended example using the 1) method
> implemented in plpgsql.
> > Let me know if you want to have a look at it.
> >
>
> If you're using 7.3.x, and don't mind a function
> based (vs pure sql
> based) approach, take a look at crosstab() in
> contrib/tablefunc.
I looked but could not find.
Where Can I find this?
>
> It has a limitation in that the data source query
> must provide for
> "missing" rows. In other words, if your query
> produces:
>
> id1 cat1 val
> id1 cat2 val
> id2 cat1 val
> id2 cat2 val
> id2 cat3 val
>
> and you specify 3 catagory columns to the crosstab
> function, then
> crosstab() will not give the result you're probably
> expecting. I
> typically work around that by doing a sub-select
> that is the
> cross-product of (distinct id) and (distinct cat),
> and then left joining
> that to the actual data. That will produce somthing
> like:
>
> id1 cat1 val
> id1 cat2 val
> id1 cat3 NULL
> id2 cat1 val
> id2 cat2 val
> id2 cat3 val
>
> For large numbers of rows and columns (at least with
> my data) I've found
> that crosstab() provides a significant performance
> boost.
Cool, if I can find a link to compiling the Crosstab
function.
tia,
.V
>
> HTH,
>
> Joe
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
=====
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: maillist@basebeans.com
Subject: Re: Table Pivot
In-Reply-To: <007a01c2d5de$a2958880$6e00a8c0@dell150>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox