>> 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
>
>
> That is documented behavior. See:
> http://www.postgresql.org/docs/8.4/interactive/tablefunc.html
>
> You probably want the other form of crosstab
>
>>
> F.33.1.4. crosstab(text, text)
Thanks a lot for the help. Indeed, that should be the one which should do it. Unfortunately, I ran into an error
message:
I have this query:
SELECT * FROM
crosstab(
'SELECT
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 = 1965 OR d.year_start = 2003 OR d.year_start = 2007 )
ORDER BY 1',
'SELECT
DISTINCT ''y_'' || year_start AS year
FROM
co2_total_cdiac AS d
WHERE
(d.year_start = 1960 OR d.year_start = 1965 OR d.year_start = 2003 OR d.year_start = 2007 )
ORDER BY 1'
)
AS ct(name varchar, y_1960 numeric, y_1965 numeric, y_2003 numeric, y_2007 numeric)
Now, I get an error message:
ERROR: invalid input syntax for integer: "SELECT
DISTINCT 'y_' || year_start AS year
FROM
co2_total_cdiac AS d
WHERE
(d.year_start = 1960 OR d.year_start = 1965 OR d.year_start = 2003 OR d.year_start = 2007 )
ORDER BY 1"
LINE 15: 'SELECT
^
I need to convert the integer values for the years into column names, i.e. "1965" into "y_1965". How do I achieve this
then?
Thanks for any help!
Stef
