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 this then?
>
> 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