GETTING NULLS WHEN USING PIVOT [message #653894] |
Thu, 21 July 2016 08:00 |
|
vijay1255
Messages: 2 Registered: July 2016
|
Junior Member |
|
|
Dear Experts,
Am working on pivot in oracle SQL.I have issues as am getting nulls instead of data after doing pivot.
Please find the attachment of Sample source data, Query and the output.
Kindly provide your inputs and let me know if i need to change anything in query to get the desired output.
Thanks in advance.
Best Regards,
Vijay Vanamala.
[mod-edit: image inserted into messaged body by bb]
[Updated on: Thu, 21 July 2016 18:19] by Moderator Report message to a moderator
|
|
|
|
|
|
Re: GETTING NULLS WHEN USING PIVOT [message #653933 is a reply to message #653931] |
Fri, 22 July 2016 07:33 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
I would strongly suggest that instead of a column for year and a column for an abbreviated month you only use a single DATE column. You can sort on it and it is very easy to produce the exact same results and you can't load garbage into it.
select *
from (select to_char(DATE_COLUMN,'YYYY') YEAR,
substr(TO_CHAR(DATE_COLUMN,'Month'),1,4) monthe,
Amount
from pivot_test s)
PIVOT
(
sum(Amount)
for monthe IN ('Janu','Febr','Marc','Apri','May','June','July','Augu','Sept','Octo','Nove','Dece')
);
|
|
|
|
|