Re: Problem with Crosstab (Concatenate Problem)

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