Crosstab Problems

From: Stefan Schwarzer <stefan(dot)schwarzer(at)grid(dot)unep(dot)ch>
To: pgsql-general(at)postgresql(dot)org
Subject: Crosstab Problems
Date: 2007-10-18 13:24:17
Message-ID: 4E6E765B-2899-4B85-9131-A8847FB06305@grid.unep.ch
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-patches

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Dietmar Maurer 2007-10-18 14:06:50 autovacuum and locks
Previous Message Bill Moran 2007-10-18 12:24:49 Re: a failover scenario

Browse pgsql-patches by date

  From Date Subject
Next Message Tom Lane 2007-10-18 13:52:23 Re: Why copy_relation_data only use wal whenWALarchivingis enabled
Previous Message Heikki Linnakangas 2007-10-18 10:58:11 Re: Why copy_relation_data only use wal whenWALarchivingis enabled