All (running 11.2 on OEL 5),
I am struggling figuring out how to get the result I want with the following data:
CREATE TABLE REGION_LOOKUP
( REGION_ID NUMBER NOT NULL ENABLE,
REGION VARCHAR2(5) NOT NULL ENABLE,
PRIMARY KEY ("REGION_ID") ENABLE
)
/
CREATE TABLE IND_REVENUE
( ID NUMBER,
IND_REV_DATE VARCHAR2(30),
IND_REVENUE NUMBER,
REGION_ID NUMBER,
CONSTRAINT IND_REVENUE_PK PRIMARY KEY (ID) ENABLE
)
/
INSERT INTO REGION_LOOKUP (REGION_ID, REGION VALUES(1,'EMEA');
INSERT INTO REGION_LOOKUP (REGION_ID, REGION VALUES(2,'LAD');
INSERT INTO REGION_LOOKUP (REGION_ID, REGION VALUES(3,'APAC');
INSERT INTO REGION_LOOKUP (REGION_ID, REGION VALUES(4,'NAS');
INSERT INTO REGION_LOOKUP (REGION_ID, REGION VALUES(5,'JAPAN');
/
INSERT INTO IND_REVENUE VALUES(1,'10-Jun',73.10,4);
INSERT INTO IND_REVENUE VALUES(2,'10-Jul',49.30,4);
INSERT INTO IND_REVENUE VALUES(3,'10-Jun',3.20,2);
INSERT INTO IND_REVENUE VALUES(4,'10-Jul',0.30,2);
INSERT INTO IND_REVENUE VALUES(5,'10-Jun',28.60,3);
INSERT INTO IND_REVENUE VALUES(6,'10-Jul',12.40,3);
INSERT INTO IND_REVENUE VALUES(7,'10-Jun',64.00,1);
INSERT INTO IND_REVENUE VALUES(8,'10-Jul',19.80,1);
INSERT INTO IND_REVENUE VALUES(9,'10-Jun',6.60,5);
INSERT INTO IND_REVENUE VALUES(10,'10-Jul',4.70,5);
/
The result I'd like to get is as follows. The date column 10-Jun and 10-Jul is "dynamic" as there will be a new column every month.
Region 10-Jun 10-Jul Total
APAC $28.6 $12.4 $41.0
EMEA $64.0 $19.8 $83.8
JAPAN $6.6 $4.7 $11.3
LAD $3.2 $0.3 $3.5
NAS $73.1 $49.3 $122.4
Total $175.5 $86.5 $262.0
I am thinking something like this (I am stuck) but this obviously doesn't work at all and is also hard coded with regards to the columns.
select rn, 10-Jun, 10-Jul, (10-Jun + 10-Jul) as Total from
(select RL.REGION_NAME as rn, RL.REGION as re, IR.IND_REVENUE as rev
from REGION_LOOKUP RL,
IND_REVENUE IR where IR.region_id = RN.region_id)
pivot (SUM(rev) for rn in
('10-Jun' as 10-Jun,
'10-Jul' as 10-Jul))
Any great ideas?
Cheers,
Andy
Fixed the insert statements, sorry about that. Copy and paste before coffee isn't good.