Create a Pivot Report in Oracle Version 10g SQL

seenall
seenall used Ask the Experts™
on
Trying to create a Pivot from three columned query / table  data - period/language/count_of
   Using Oracle 10g
   Don't want to use Case Statements as this will be a template for many other reports
   11g has a wonderful pivot function - but we have 10g so out of luck there
   Have also tried ref cursors - don't go there!
   So here is an Oracle model statement - "nearly works"
   My present bible that led me here is as follows
   http://technology.amis.nl/blog/300/pivoting-in-sql-using-the-10g-model-clause
   Which has got me well on the road
   But am not able to see the cause of the problem that the query (below) is returning zeros
   Have tweaked the dimension, partition and still won't play
   Help much appreciated

drop table myChecks purge;
create table myChecks (
 Period 			 date
, Language			 VARCHAR2(20)
, count_of 	    NUMBER )
compress nologging;

insert into myChecks values ( to_date(20100702,'yyyymmdd'), 'Brazilian Portuguese', 8);
insert into myChecks values ( to_date(20100702,'yyyymmdd'), 'Bulgarian', 1);
insert into myChecks values ( to_date(20100702,'yyyymmdd'), 'Chinese', 3);
insert into myChecks values ( to_date(20100702,'yyyymmdd'), 'Danish', 1);
insert into myChecks values ( to_date(20100702,'yyyymmdd'), 'Dutch', 4);
insert into myChecks values ( to_date(20100702,'yyyymmdd'), 'English', 85);
insert into myChecks values ( to_date(20100702,'yyyymmdd'), 'Finnish', 2);
insert into myChecks values ( to_date(20100702,'yyyymmdd'), 'French', 4);
insert into myChecks values ( to_date(20100702,'yyyymmdd'), 'German', 51);
insert into myChecks values ( to_date(20100702,'yyyymmdd'), 'Greek', 3);
commit;

select * from myChecks;

/*
Source data easy view
PERIOD	  LANGUAGE		 COUNT_OF
--------- -------------------- ----------
02-JUL-10 Brazilian Portuguese		8
02-JUL-10 Bulgarian			1
02-JUL-10 Chinese			3
02-JUL-10 Danish			1
02-JUL-10 Dutch 			4
02-JUL-10 English		       85
02-JUL-10 Finnish			2
02-JUL-10 French			4
02-JUL-10 German		       51
02-JUL-10 Greek 			3
*/


/* Main Pivot Query */
select period , German , French , Spanish , Dutch
 , Italian , Swedish  , Greek  , Russian , Portugese ,
Danish , SimplifiedChinese     , Chinese
  from myChecks
  model IGNORE NAV unique single reference
  return updated rows
  partition by (Period )
  dimension by (Language)
  measures (0  count_of
     , 0 Chinese  , 0 Danish , 0 Dutch , 0 French 
     , 0 German     , 0 Greek  , 0 Italian  
     , 0 Portugese  , 0 Russian , 0 SimplifiedChinese 
     , 0 Spanish  , 0 Swedish)
  rules
  (
      Chinese [0] = count_of [1]
     , Danish [0] = count_of [2]
     , Dutch [0] = count_of [3]
     , French [0] = count_of [4]
     , German [0] = count_of [5]
     , Greek [0] = count_of [6]
     , Italian [0] = count_of [7]
     , Portugese [0] = count_of [8]
     , Russian [0] = count_of [9]
     , SimplifiedChinese [0] = count_of [10]
     , Spanish [0] = count_of [11]
     , Swedish [0] = count_of [12]
  )
  order by 1 desc;
/*   Results below   
PERIOD	      GERMAN	 FRENCH    SPANISH	DUTCH	 ITALIAN    SWEDISH	 GREEK	  RUSSIAN  PORTUGESE	 DANISH SIMPLIFIEDCHINESE    CHINESE
--------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------------- ----------
02-JUL-10	   0	      0 	 0	    0	       0	  0	     0		0	   0	      0 		0	   0
*/

drop table myChecks purge;

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi,
First of all, congratulations, you provided a complete test case.

There are 2 points:
1)  'measures (0  count_of' assigns 0 to count_of so you loose the value
2) in the rules what is inside [] should be a language value (because dimension by language)

So here are the modifications:

  measures (count_of
     , 0 Chinese  , 0 Danish , 0 Dutch , 0 French
     , 0 German     , 0 Greek  , 0 Italian
     , 0 Portugese  , 0 Russian , 0 SimplifiedChinese
     , 0 Spanish  , 0 Swedish)
  rules
  (
      Chinese ['*'] = count_of ['Chinese']
     , Danish ['*'] = count_of ['Danish']
     , Dutch ['*'] = count_of ['Dutch']
     , French ['*'] = count_of ['French']
...

Note that I have replaced 0 by '*' because it is not a number but a language name you assign to the line that cumulates all languages. You can put whatever you want as you don't have it in the select, but not a number.

I guess you thought that referencing rows were done with numbers. This is not the case, they are referenced by the dimension.

Regards,
Franck.

Author

Commented:
Fantastic!
Gof concerned when I applied it my real scenario - my source data was not aggregated (shared table used by other reports)
"                      *
ERROR at line 13:
ORA-32638: Non unique addressing in MODEL dimensions
blah"

This is going to add a lot to my arsenal of reporting with using clunky reporting server applications

Thanks! Very Much!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial