Re: Problem with Crosstab (Concatenate Problem) - Mailing list pgsql-general

On 11/01/2010 06:24 AM, Stefan Schwarzer wrote:
> 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)


HTH,

Joe

--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support