Re: Problem with Crosstab (Concatenate Problem)

[Stefan Schwarzer <stefan.schwarzer@xxxxxxxx>]

>> 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



-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general