Re: Crosstab Problems

On 10/18/07, Stefan Schwarzer <stefan.schwarzer@grid.unep.ch> wrote:
> But when re-doing the query now without the JOIN, it works (almost):
>
> SELECT
>     *
> FROM
>     crosstab(
>        'SELECT
>             id_country AS id,
>             year_start AS year,
>             value
>         FROM
>             agri_area AS d
>         WHERE
>             year_start = 2003 OR year_start = 2002 OR year_start =
> 2001 ORDER BY year_start ASC, id_country ASC;'
>         , 3)
> AS ct(id int2, y_2003 numeric, y_2002 numeric, y_2001 numeric)
>
> Now, the problem is that it lists three times the IDs, and only the
> first year column is filled with values. The other two year columns
> stay empty.

You missed this point in the docs:

Notes

  1. The sql result must be ordered by 1,2.
Change your order by to that and it works fine.