From: Joe Conway <mail@joeconway.com>
To: Christoph Haller <ch@rodos.fzk.de>
Cc: vc@basebeans.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Table Pivot
Date: Fri, 14 Feb 2003 07:35:50 -0800
Message-ID: <3E4D0CD6.7040309@joeconway.com> (raw)
In-Reply-To: <3E4CBB60.F32C76D5@rodos.fzk.de>
References: <3E4CBB60.F32C76D5@rodos.fzk.de>
Christoph Haller wrote:
>
> 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.
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.
HTH,
Joe
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: mail@joeconway.com, ch@rodos.fzk.de, vc@basebeans.com
Subject: Re: Table Pivot
In-Reply-To: <3E4D0CD6.7040309@joeconway.com>
* 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