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)