Re: Problem with Crosstab (Concatenate Problem)

Поиск
Список
Период
Сортировка
От Joe Conway
Тема Re: Problem with Crosstab (Concatenate Problem)
Дата
Msg-id 4CCED05B.6010707@joeconway.com
обсуждение исходный текст
Ответ на Re: Problem with Crosstab (Concatenate Problem)  (Stefan Schwarzer <stefan.schwarzer@unep.org>)
Ответы Re: Problem with Crosstab (Concatenate Problem)  (Stefan Schwarzer <stefan.schwarzer@unep.org>)
Список 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


Вложения

В списке pgsql-general по дате отправления:

Предыдущее
От: Andy Colson
Дата:
Сообщение: Re: async queries in Perl and poll()/select() loop - how to make them work together?
Следующее
От: zhong ming wu
Дата:
Сообщение: Is this a known feature of 8.1 SSL connection?