Re: Crosstab Problems

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

I use crosstab for a rather large weekly report in our db and it works
fine, however, you can't feel it nulls. It needs all the holes filled
in, so to speak.

In mine I had to use generate_series to make sure all the rows were
there, then coalesce to make sure there were no nulls. You might need
to do something like that in yours. I'm trying it out now.