Re: Table Pivot
| От | Vic Cekvenich |
|---|---|
| Тема | Re: Table Pivot |
| Дата | |
| Msg-id | 007a01c2d5de$a2958880$6e00a8c0@dell150 обсуждение исходный текст |
| Ответ на | Table Pivot ("V. Cekvenich" <vc@basebeans.com>) |
| Ответы |
Re: Table Pivot
|
| Список | pgsql-sql |
> > 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
=====
В списке pgsql-sql по дате отправления: