Re: crosstab help - Mailing list pgsql-sql

From Andreas Gaab
Subject Re: crosstab help
Date
Msg-id 48DA836F3865C54B8FBF424A3B775AF667451998F1@Exchange-Server
Whole thread Raw
In response to crosstab help  (John Fabiani <johnf@jfcomputer.com>)
Responses Re: crosstab help
List pgsql-sql
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


pgsql-sql by date:

Previous
From: John Fabiani
Date:
Subject: crosstab help
Next
From: John Fabiani
Date:
Subject: Re: crosstab help