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 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:
) Can anyone give me a hint in which direction to search for a solution? Thanks a lot! Stefan |
