From: Andreas Gaab <A.Gaab@scanlab.de>
To: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Date: Fri, 24 Feb 2012 09:48:03 +0100
Subject: Re: crosstab help
Thread-Topic: [SQL] crosstab help
Thread-Index: Aczyz9ccSabFWrCtSLyYM7Nd3hMzEwAAMqMg
Message-ID: <48DA836F3865C54B8FBF424A3B775AF667451998F2@Exchange-Server>
References: <2071893.Jsp6YpFfLm@linux-12>
	<48DA836F3865C54B8FBF424A3B775AF667451998F1@Exchange-Server>
	<1450731.eLkFA9G6tq@linux-12>
In-Reply-To: <1450731.eLkFA9G6tq@linux-12>
Accept-Language: de-DE
Content-Language: de-DE
acceptlanguage: de-DE
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0

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

Andreas

-----Urspr=FCngliche Nachricht-----
Von: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@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?   A=
nd=20
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,
>=20
> the return type of the crosstab must be defined correctly, according=20
> to the number of expected columns.
>=20
> Try following (untested):
>=20
> select * from crosstab(
> 'select item_number::text as row_name,=20
> 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)
>=20
> Regards,
> Andreas
>=20
>=20
>=20
> -----Urspr=FCngliche Nachricht-----
> Von: pgsql-sql-owner@postgresql.org=20
> [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
>=20
> 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
>=20
>=20
> where
> item_number text
> week_of date
> planned_qoh integer
>=20
> I have a function that returns the table as above:
>=20
> chromasun._chromasun_totals(now()::date)
>=20
> I want to see
>=20
> 00005   2012-02-05   2012-02-12    2012-02-19
>                     30                  40                 50
>=20
> This is what I have tried (although, I have tired many others)
>=20
> select * from crosstab('select item_number::text as row_name,
> to_char(week_of,''MM-DD-YY'') as bucket, planned_qoh::integer as=20
> buckvalue from xchromasun._chromasun_totals(now()::date)') as=20
> ct(item_number text, week_of date,  planned_qoh integer)
>=20
> I get
> ERROR:  return and sql tuple descriptions are incompatible
>=20
> What am I doing wrong?
>=20
> Johnf
>=20
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make=20
> changes to your subscription:=20
> 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