From: John Fabiani <johnf@jfcomputer.com>
To: pgsql-sql@postgresql.org
Subject: Re: crosstab help
Date: Fri, 24 Feb 2012 00:56:55 -0800
Message-ID: <4252864.NjuqYTl098@linux-12>
User-Agent: KMail/4.7.2 (Linux/3.1.9-1.4-desktop; KDE/4.7.2; x86_64; ; )
In-Reply-To: <48DA836F3865C54B8FBF424A3B775AF667451998F2@Exchange-Server>
References: <2071893.Jsp6YpFfLm@linux-12> <1450731.eLkFA9G6tq@linux-12>
	<48DA836F3865C54B8FBF424A3B775AF667451998F2@Exchange-Server>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain; charset="iso-8859-1"

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 a=
nd
> column headers individually for each query or define some custom
> function...
>=20
> Andreas
>=20
> -----Urspr=FCngliche Nachricht-----
> Von: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresq=
l.org]
> Im Auftrag von John Fabiani Gesendet: Freitag, 24. Februar 2012 09:39=

> An: pgsql-sql@postgresql.org
> Betreff: Re: [SQL] crosstab help
>=20
> That worked!  However, I need the actual date to be the column headin=
g? =20
> And of course the dates change depending on the date passed to the
> function: xchromasun._chromasun_totals(now()::date)
>=20
> So how do I get the actual dates as the column header?
> johnf
>=20
> On Friday, February 24, 2012 09:27:38 AM Andreas Gaab wrote:
> > Hi,
> >=20
> > the return type of the crosstab must be defined correctly, accordin=
g
> > to the number of expected columns.
> >=20
> > Try following (untested):
> >=20
> > 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)
> >=20
> > Regards,
> > Andreas
> >=20
> >=20
> >=20
> > -----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:=
11
> > An: pgsql-sql@postgresql.org
> > Betreff: [SQL] crosstab help
> >=20
> > I have a simple table
> > item_number  week_of               planned_qoh
> > ------------------  ------------------       ------------------
> > 00005=09        2012-02-05           30
> > 00005=09        2012-02-12           40
> > 00005=09        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
> >=20
> >                     30                  40         =20
> >                           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
> > buckvalue from xchromasun._chromasun_totals(now()::date)') as
> > 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
> > changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-sql
>=20
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make ch=
anges
> to your subscription: http://www.postgresql.org/mailpref/pgsql-sql