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