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@xxxxxx>
I'm looking for a way to generate cross tab with 3 columns for every store wherenumber of stores in not hard coded.Every store info should contain 3 columns:turnoverbudgetbudget percent (=turnover/budget*100)Result should look like:Acc st1turnover st1budget st1percent ... stNturnover st1budget stNpercent311 100 200 50 200 ... 300 67312 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.
