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.