Re: 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(at)hot(dot)ee>

> 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.
>
>
>
>
>
>
>