Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Pivot query problem

ATael-OracleAug 11 2010 — edited Aug 11 2010
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.
This post has been answered by Frank Kulash on Aug 11 2010
Jump to Answer

Comments

737905
Your insert statements are incorrect. Could you please post the correct inserts?

Edited by: AP on Aug 11, 2010 7:59 AM
737905
try this:
SELECT (select region from region_lookup where region_id = x.region_id) region,
       10_Jun,
       10_Jul,10_Jun + 10_Jul 
FROM (SELECT region_id, 
             SUM(CASE WHEN IND_REV_DATE = '10-Jun' THEN IND_REVENUE ELSE 0 END) 10_Jun, 
            SUM(CASE WHEN IND_REVENUE = '10-Jul' THEN IND_REVENUE ELSE 0 END) 10_Jul 
FROM IND_REVENUE GROUP BY region_id) x
/
PS: Not tested

Edited by: AP on Aug 11, 2010 8:07 AM
ATael-Oracle
Sorry about that, I've fixed them.

-Andy
ATael-Oracle
The statement gives me:
ORA-00911: invalid character
Cheers,
Andy
Aketi Jyuuzou
I llke rollup using Composite Columns :D
select case grouping(a.REGION_ID)
       when 1 then 'Total' else a.REGION end as Region,
sum(decode(b.IND_REV_DATE,'10-Jun',b.IND_REVENUE)) as "10-Jun",
sum(decode(b.IND_REV_DATE,'10-Jul',b.IND_REVENUE)) as "10-Jul",
sum(b.IND_REVENUE) as Total
  from REGION_LOOKUP a Join IND_REVENUE b
    on a.REGION_ID = b.REGION_ID
group by rollup((a.REGION_ID,a.REGION))
order by grouping(a.REGION_ID),a.REGION;

REGIO  10-Jun  10-Jul  TOTAL
-----  ------  ------  -----
APAC     28.6    12.4     41
EMEA       64    19.8   83.8
JAPAN     6.6     4.7   11.3
LAD       3.2      .3    3.5
NAS      73.1    49.3  122.4
Total   175.5    86.5    262
1 person found this helpful
ATael-Oracle
Thanks, this works like a charm but the column containing 10-Jun and 10-Jul today will soon have another column called 10-Aug etc. So the number of columns will extend every month, basically a non known number of columns.

Ideas?

Cheers,
Andy
Frank Kulash
Hi, Andy,
A Tael wrote:
Thanks, this works like a charm but the column containing 10-Jun and 10-Jul today will soon have another column called 10-Aug etc. So the number of columns will extend every month, basically a non known number of columns.
See the following thread for several ways to deal with a variable number of columns:
3529823
1 person found this helpful
ATael-Oracle
A nice solution, but isn't there a way to avoid the spooling into a file?

Cheers,
Andy
Frank Kulash
Answer
Hi, Andy,
A Tael wrote:
A nice solution,
Thanks, but which solution do you mean? There were at least 4 different soltuions on that page.
but isn't there a way to avoid the spooling into a file?
Do you mean the dynamic SQL solution? Yes, SPOOLing is just one way to do dynamic SQL.
In SQL*Plus you can write the variable part of the query to a substitution variable (see below).
In PL/SQL, you can assemble a SELECT statement in VARCHAR2 variables.
PROMPT	==========  2. Dynamic Pivot using Substitution Variable  ==========

--	*****  Preliminary Query:  *****

COLUMN	sql_txt_col	NEW_VALUE	sql_txt

WITH	all_jobs	AS
(
	SELECT DISTINCT
		job
	,	DENSE_RANK () OVER (ORDER BY job)	AS r_num
	FROM	scott.emp
)	
SELECT	SYS_CONNECT_BY_PATH ( job || '''	THEN 1 END) AS '
				  || job
				  || '_CNT'
				  || CHR (10)			-- Newline, for legibility only
			    , ', COUNT (CASE WHEN job = '''	-- Delimiter, goes before each entry
			    ) 	       	     	      	AS sql_txt_col
FROM	all_jobs
WHERE	CONNECT_BY_ISLEAF	= 1
START WITH	r_num = 1
CONNECT BY	r_num = PRIOR r_num + 1
;

--	*****  Main Query  *****

SELECT	deptno
&sql_txt	-- Ends with newline, so occupy that line
FROM	scott.emp
GROUP BY	deptno
ORDER BY	deptno
;

/*
EXPLANATION:

Using a substitution variable is very similar to using a script.
The main difference is that the output of the preliminary query has to
go into one row.  This is done with SYS_CONNECT_BY_PATH, and getting
that requires that the jobs be numbered with consecutive integers, 1, 2, 3, ...
which we get from DENSE_RANK.

The NEWLINE character was added just to make the output line easier
to read during debugging.
*/
Marked as Answer by ATael-Oracle · Sep 27 2020
1 - 9
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Sep 8 2010
Added on Aug 11 2010
9 comments
1,945 views