Re: Crosstab Problems - Mailing list pgsql-general

> Could you provide a self-contained test case for this?  There's not
> really enough information here for someone else to duplicate the
> problem.  Also, which PG version are you using?

Wasn't sure what you ment with "a self containted test case". Is it
the raw data?

Here is a SQL dump for the table. One can just neglect the JOIN with
the countries table (which just replaces the country id with the
country name):

http://geodata.grid.unep.ch/download/sql_agri_area.sql.zip

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.

Thanks for any advice!

Stef