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-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] > Im Auftrag von John Fabiani Gesendet: Freitag, 24. Februar 2012 09:11 > An: pgsql-sql@postgresql.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@postgresql.org) To make changes > to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
