Re: Table Pivot

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

=====