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)
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
