Hi there,
successfully installed the tablefunc package.
Now, I would like to transform this kind of result based on a normal SQL:
c_name | year | value
---------------------------------------
Germany | 2001 | 123
Germany | 2002 | 125
Germany | 2003 | 128
Germany | 2004 | 132
Germany | 2005 | 135
Italy | 2001 | 412
Italy | 2002 | 429
Italy | 2003 | 456
Italy | 2004 | 465
Italy | 2005 | 477
to this one:
c_name | 2001 | 2002 | 2003 | 2004 | 2005
------------------------------------------------------------------------
Germany | 123 | 125 .....
Italy | 412 | .....
I use this SQL statement:
SELECT
*
FROM
crosstab(
'SELECT
c.name AS name,
year_start AS year,
value
FROM
agri_area AS d
LEFT JOIN
countries AS c ON c.id = id_country
WHERE
year_start = 2003 OR
year_start = 2002 OR
year_start = 2001
ORDER BY
name ASC,
year_start ASC;'
, 3)
AS ct(name varchar, y_2003 numeric, y_2002 numeric, y_2001 numeric)
I had a couple of problems getting there. But now that I have the feeling that this is OK, it tells me this:
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
Can anyone tell me why? And how to get it right? Thanks for any advice!
Stef