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

Hm...

I am not sure it is possible at all and with just 1 column for crosstab
(instead of 3) to return table with undefined No of columns (to number of
stores do not be hardcoded)...

At least you must define your return type...

Problem is known to me, and we have solved it on some way... which is not
acceptable as generic solution... I mean - works just with our app...

So, we are doing that in two steps...

first calculate how much columns we should return...No of stores in in your
Sales table (multiply 3 in your case)...

(and based on that - build the grid in UI)

then with knowing that - we select full "table" with simple 3 functions:
CalcSales(coount, Store), getBudget(account, Store)

and then build dynamic query with those 3 functions many times as we have
stores as columns...

Kind Regards,

Misa

2012/2/6 Andrus <kobruleht2(at)hot(dot)ee>

> I'm looking for a way to generate cross tab with 3 columns for every
> store where
> number of stores in not hard coded.
> Every store info should contain 3 columns:
>
> turnover
> budget
> budget percent (=turnover/budget*100)
>
> Result should look like:
>
> Acc st1turnover st1budget st1percent ... stNturnover st1budget
> stNpercent
> 311 100 200 50 200 ...
> 300 67
> 312 400 500 80 600 ...
> 700 86
> ...
>
> I tried crosstab from tablefunc but it allows only single value in every
> crosstabled column.
>
> How to show 3 values in every column: sales, budget and percent in this
> order?
>
> Tables are:
>
> create table sales (
> account char(10),
> store char(10),
> sales numeric(12,2) );
> insert into sales values
> ('311','ST1',100)... ('311','STN',200)
> ('312','ST1',400)... ('312','STN',600);
>
> create table budget (
> account char(10),
> store char(10),
> budget numeric(12,2) );
> insert into budger values
> ('311','ST1',200)... ('311','STN',300)
> ('312','ST1',500)... ('312','STN',700);
>
> Some account and store values may be missing from tables.
>
> Andrus.
>