Thread: crosstab help

crosstab help

From
John Fabiani
Date:
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


Re: crosstab help

From
Andreas Gaab
Date:
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
fromxchromasun._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


Re: crosstab help

From
John Fabiani
Date:
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-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


Re: crosstab help

From
Andreas Gaab
Date:
As far as I know you must define the numbers (and types) of columns and column headers individually for each query or
definesome custom function... 

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: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-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

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql


Re: crosstab help

From
John Fabiani
Date:
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-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?
> 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-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
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes
> to your subscription: http://www.postgresql.org/mailpref/pgsql-sql


Re: crosstab help

From
Jayadevan
Date:
Hello all ,
I have a similar situation - an EAV table.
entity_attr_id  integer          entity_id       bigint           ent_attr_value  character varying
I am trying 
select * from crosstab('select entity_id::text as row_name,
entity_attr_id::bigint as entity_attr_id , ent_attr_value::text  as
ent_attr_value from org_entity_attr_value order by 1') as ct(                
row_name text,entity_attr_id bigint,ent_attr_value text )
and get an error - 
ERROR:  return and sql tuple descriptions are incompatible





--
View this message in context: http://postgresql.1045698.n5.nabble.com/crosstab-help-tp5511994p5757977.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.