Re: Problem with Crosstab (Concatenate Problem)

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

HTH,

Joe

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