Re: Table Pivot

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