>> I need to convert the integer values for the years into column names, i.e. "1965" into "y_1965". How do I achieve
thisthen?
>
> Try something like:
>
> create table foo (
> name text,
> year_start int,
> value float8);
>
> insert into foo values('a',2010,1.23),('b',2011,2.34);
>
> SELECT * FROM
> crosstab(
> 'SELECT name, year_start, value FROM foo ORDER BY 1',
> 'SELECT DISTINCT year_start FROM foo'
> )
> AS ct(name varchar, y_2010 float8, y_2011 float8);
>
> name | y_2010 | y_2011
> ------+--------+--------
> a | | 1.23
> b | 2.34 |
> (2 rows)
Hi Joe. Thanks a lot for the suggestions. Tried it out, but same error message:
ERROR: invalid input syntax for integer: "SELECT DISTINCT year_start FROM foo"
LINE 4: 'SELECT DISTINCT year_start FROM foo'
^
Did this work for you? Then this would indeed be strange.
I wonder if the second crosstab SQL must have the same column names as the final output or not ("2010" vs. "y_2010").
Anyone can help me out? Thanks a lot for any tips!
Stef
