Re: Problem with Crosstab (Concatenate Problem)

From: Stefan Schwarzer <stefan(dot)schwarzer(at)unep(dot)org>
To: pgsql-general(at)postgresql(dot)org
Cc: Joe Conway <mail(at)joeconway(dot)com>
Subject: Re: Problem with Crosstab (Concatenate Problem)
Date: 2010-11-01 13:24:58
Message-ID: 7AD4FBC7-5066-486B-A2BD-2DFE03E2A020@unep.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Eduardas Tcpa 2010-11-01 13:41:45 Re: pgAdmin 3 index display problem
Previous Message Stefan Schwarzer 2010-11-01 13:03:19 Re: Problem with Crosstab (Concatenate Problem)