CODEWITHSUNDEEP
oracle-databasenullpivot-table
Daniel Bonetti
Daniel Bonetti

Reputation: 2426

Oracle Pivoting returning Null values for Month/Year

I'm trying to pivoting this table by month/year according to date (field DT):

  CREATE TABLE "TEMP" 
   (    "NAME" VARCHAR2(20 BYTE), 
        "DT" DATE, 
        "SZ" NUMBER(10,0)
   )

Insert into TEMP (NAME,DT,SZ) values ('A',to_date('16/01/15','DD/MM/RR'),'1');
Insert into TEMP (NAME,DT,SZ) values ('B',to_date('16/01/15','DD/MM/RR'),'2');
Insert into TEMP (NAME,DT,SZ) values ('C',to_date('16/01/15','DD/MM/RR'),'3');
Insert into TEMP (NAME,DT,SZ) values ('D',to_date('16/01/15','DD/MM/RR'),'4');
Insert into TEMP (NAME,DT,SZ) values ('A',to_date('10/01/15','DD/MM/RR'),'5');
Insert into TEMP (NAME,DT,SZ) values ('B',to_date('10/01/15','DD/MM/RR'),'6');
Insert into TEMP (NAME,DT,SZ) values ('C',to_date('10/01/15','DD/MM/RR'),'7');

using this query:

SELECT * from
  (select name, sz, dt from temp) 
  pivot (sum(sz) for dt in (to_date('01/2015', 'MM/YYYY') as "dt"))

and I receive this:

NAME                         dt
-------------------- ----------
D                               
A                               
B                                
C

However, I was excepting to get:

NAME                         dt
-------------------- ----------
D                             4 
A                             6  
B                             8   
C                            10

I've tried many things but none of them seems to work as:

...
pivot (sum(sz) for dt in ('01/2015' as "dt")) --01843. 00000 -  "not a valid month"

Do you have a clue of what I'm doing wrong?

Any help is very welcome.

Upvotes: 0

Views: 171

Answers (1)

Srini V
Srini V

Reputation: 11375

You need to use TO_CHAR and not TO_DATE

WITH pivotdata AS 
( 
       SELECT name, 
              sz, 
              To_char(dt, 'MM/YYYY') dt1 
       FROM   temp ) 
SELECT * 
FROM   pivotdata pivot (SUM(sz) FOR dt1 IN ('01/2015') );

This will return

NAME    '01/2015'
D   4
A   6
B   8
C   10

Upvotes: 2

Related Questions