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