Re: Crosstab Problems

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

Oh, great. No, haven't seen it. Now it works. Thanks a lot!

Just for the completeness, I attach the SQL.

SELECT
*
FROM
crosstab(
'SELECT
COALESCE(c.name, ''''),
year_start AS year,
value
FROM
agri_area AS d
LEFT JOIN
countries AS c ON c.id = id_country
WHERE
year_start = 2003 OR year_start = 2002 OR year_start = 2001
GROUP BY
name, id_country, year_start, value
ORDER BY 1,2;'
, 3)
AS ct(name varchar, y_2003 numeric, y_2002 numeric, y_2001 numeric)