Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Create a Pivot Report in Oracle Version 10g SQL

Avatar of seenall
seenall asked on
DatabasesOracle Database
2 Comments1 Solution1882 ViewsLast Modified:
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;
ASKER CERTIFIED SOLUTION
Avatar of Franck Pachot
Franck PachotFlag of Switzerland imageOracle DBA

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Commented:
This problem has been solved!
Unlock 1 Answer and 2 Comments.
See Answers