Re: How to create crosstab with 3 values in every crosstab column
От | Andrus |
---|---|
Тема | Re: How to create crosstab with 3 values in every crosstab column |
Дата | |
Msg-id | 6D3CC6E236244513979115C62BCD2412@dell2 обсуждение исходный текст |
Ответ на | Re: How to create crosstab with 3 values in every crosstab column (Misa Simic <misa.simic@gmail.com>) |
Ответы |
Re: How to create crosstab with 3 values in every crosstab column
|
Список | pgsql-general |
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.
В списке pgsql-general по дате отправления: