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

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.