Thanks for the insight! johnf On Friday, February 24, 2012 09:48:03 AM Andreas Gaab wrote: > 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-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] > Im Auftrag von John Fabiani Gesendet: Freitag, 24. Februar 2012 09:39 > An: pgsql-sql@postgresql.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-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
-- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
