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.
