Reputation: 2426
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
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