Re: How to create crosstab with 3 values in every crosstab column

["Marc Mamin" <M.Mamin@xxxxxxxxxxxx>]

Hello,

 

as you don't seems to need the returned column definition in Postgres, a solution may be to cast the result to text.

 

e.g.:

 

 

create or replace function get_record ()

returns setof text as

$$

  select (foo)::text from

  (values(1,'a a'),(3,'b b'))foo

$$

language sql;

 

select trim(r,'\\(\\)') from get_record () r;

 

regards,

 

Marc Mamin

 

 

From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Misa Simic
Sent: Montag, 6. Februar 2012 19:52
To: Andrus
Cc: pgsql-general@xxxxxxxxxxxxxx
Subject: Re: [GENERAL] How to create crosstab with 3 values in every crosstab column

 

Hi,

 

Well, I think you will need to write your own function(s) which will solve your particular case... 

 

There are two ways explaind in last mails... Dynamic SQL or direct export to file...

 

Kind Regards,

 

Misa

2012/2/6 Andrus <kobruleht2@xxxxxx>

Thank you.

 

the point is - it is not possible to get unknown no of columns in 1 SQL query...
i.e.
Account, Store, Amount
100, St1, 1000.00
100, St2, 2000.00
to get:
Acount,    St1     ,      St2
100,     1000.00      2000.00
to get that in your query... St1 and St2 - must be hardcoded... (is there 1 column per Store, or 3 columns per store it >is less important...)
if it St1 and St2 are hardcoded in query, even if in table is:
Account, Store, Amount
100, St1, 1000.00
100, St2, 2000.00
100, St3, 3000.00
We would get the same result...actually if we want St3 we need to change our query and add St3 in it...
that is the reason why we use Dynamic SQL to build the query...
when you build your Dynamic SQL query... you could use COPY (dynamicQuery) TO CSV file...
or instead of to build dynamic query, you can export directly to file...

 

I din't knwo this. This seems very serious limitation which makes crosstab useless .
I tried

create temp table sales (
 account char(10),
 store char(10),
 sales  numeric(12,2) ) on commit drop;

insert into sales values
('311','ST1',100), ('311','STN',200),
('312','ST1',400), ('312','STN',600);

select * from
crosstab('select * from sales', 'select distinct store from sales' ) x

and got error

ERROR:  a column definition list is required for functions returning "record"

Can we use something like

select * from
 dynamicwrapper( crosstab('select * from sales', 'select distinct store from sales' ))  x

Where to find generic dynamicwrapper stored procedure which fixes this by building dynamic query itself or other idea ?

Andrus.