From: Andreas Gaab <A.Gaab@scanlab.de>
To: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: crosstab help
Date: Fri, 24 Feb 2012 09:27:38 +0100
Message-ID: <48DA836F3865C54B8FBF424A3B775AF667451998F1@Exchange-Server> (raw)
In-Reply-To: <2071893.Jsp6YpFfLm@linux-12>
References: <2071893.Jsp6YpFfLm@linux-12>
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@postgresql.org [mailto:pgsql-sql-owner@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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: A.Gaab@scanlab.de
Subject: Re: crosstab help
In-Reply-To: <48DA836F3865C54B8FBF424A3B775AF667451998F1@Exchange-Server>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox