Re: Crosstab Problems

On 10/18/07, Stefan Schwarzer <stefan(dot)schwarzer(at)grid(dot)unep(dot)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.