[GENERAL] Problem with Crosstab - Allocating value to wrong column

[Stefan Schwarzer]

Hi there,

I have a database with statistical tables which look like this:

id    |    year_start   |    value
3             1960              736.1
3             1961              723.3
....
4             1960              123.4


Years can cover 40 years for each of the countries (ID) and each (variable) 
table .

Now, if for a given year there is no value, expressed not in form of NoData, 
but as missing line in the table, than I have a problem with this SQL: 

SELECT  
        * 
FROM 
        crosstab( 'SELECT 
                                        COALESCE(c.name, '''') AS name,  
                                        d.year_start AS year, 
                                        d.value AS value         
                                FROM 
                                        co2_total_cdiac AS d 
                                RIGHT JOIN 
                                        countries_view AS c ON c.id = 
d.id_country 
                                WHERE 
                                        (d.year_start = 1960 OR d.year_start = 
2007 ) 
                                ORDER BY 
                                        1,2;', 3) AS ct (name varchar, y_1960 
numeric, y_2007 numeric)


For one of the countries, I have a value for 2007, but not for 1960. When using 
only the inner query, than I see one line: Andorra - 2007 - 539
But when running the whole SQL, the value for year 2007 get's allocated to the 
year 1960. The table looks as follows:

name     |    y_1960    |   y_2007
Andorra         539            NULL



(Not sure if the copy/pasted part displays correct:

name    y_1960  y_2007
Afghanistan     
415
716
Albania 
2026
4246
Algeria 
6166
140234
Andorra 
539
NULL
)


Can anyone give me a hint in which direction to search for a solution?

Thanks a lot!

Stefan