> > 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
