Re: crosstab help

As far as I know you must define the numbers (and types) of columns and column headers individually for each query or define some custom function...

Andreas

-----Ursprüngliche Nachricht-----
Von: pgsql-sql-owner(at)postgresql(dot)org [mailto:pgsql-sql-owner(at)postgresql(dot)org] Im Auftrag von John Fabiani
Gesendet: Freitag, 24. Februar 2012 09:39
An: pgsql-sql(at)postgresql(dot)org
Betreff: Re: [SQL] crosstab help

That worked! However, I need the actual date to be the column heading? And
of course the dates change depending on the date passed to the function:
xchromasun._chromasun_totals(now()::date)

So how do I get the actual dates as the column header?
johnf
On Friday, February 24, 2012 09:27:38 AM Andreas Gaab wrote:
> Hi,
>
> the return type of the crosstab must be defined correctly, according
> to the number of expected columns.
>
> Try following (untested):
>
> select * from crosstab(
> 'select item_number::text as row_name,
> to_char(week_of,''MM-DD-YY'')::date
> as bucket, planned_qoh::integer as buckvalue from
> xchromasun._chromasun_totals(now()::date)') as ct(item_number text,
> week_of_1 date, week_of_2 date, week_of_3 date)
>
> Regards,
> Andreas
>
>
>
> -----Ursprüngliche Nachricht-----
> Von: pgsql-sql-owner(at)postgresql(dot)org
> [mailto:pgsql-sql-owner(at)postgresql(dot)org]
> Im Auftrag von John Fabiani Gesendet: Freitag, 24. Februar 2012 09:11
> An: pgsql-sql(at)postgresql(dot)org
> Betreff: [SQL] crosstab help
>
> I have a simple table
> item_number week_of planned_qoh
> ------------------ ------------------ ------------------
> 00005 2012-02-05 30
> 00005 2012-02-12 40
> 00005 2012-02-19 50
>
>
> where
> item_number text
> week_of date
> planned_qoh integer
>
> I have a function that returns the table as above:
>
> chromasun._chromasun_totals(now()::date)
>
> I want to see
>
> 00005 2012-02-05 2012-02-12 2012-02-19
> 30 40 50
>
> This is what I have tried (although, I have tired many others)
>
> select * from crosstab('select item_number::text as row_name,
> to_char(week_of,''MM-DD-YY'') as bucket, planned_qoh::integer as
> buckvalue from xchromasun._chromasun_totals(now()::date)') as
> ct(item_number text, week_of date, planned_qoh integer)
>
> I get
> ERROR: return and sql tuple descriptions are incompatible
>
> What am I doing wrong?
>
> Johnf
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org) To make
> changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql

--
Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql