Home » SQL & PL/SQL » SQL & PL/SQL » GETTING NULLS WHEN USING PIVOT
GETTING NULLS WHEN USING PIVOT [message #653894] Thu, 21 July 2016 08:00 Go to next message
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.
/forum/fa/13199/0/


[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 #653895 is a reply to message #653894] Thu, 21 July 2016 08:03 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: GETTING NULLS WHEN USING PIVOT [message #653897 is a reply to message #653894] Thu, 21 July 2016 08:14 Go to previous messageGo to next message
Michel Cadot
Messages: 67949
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

janu != Janu

With any SQL or PL/SQL question, please, Post a working Test case: create statements for all objects so that we will be able work to reproduce what you have.

Re: GETTING NULLS WHEN USING PIVOT [message #653931 is a reply to message #653897] Fri, 22 July 2016 07:04 Go to previous messageGo to next message
vijay1255
Messages: 2
Registered: July 2016
Junior Member
Thank you Michel,

Yes i did corrected the code and am getting the result.
Next time, i will make sure to post complete working Test Case.

Thanks again Smile

Best Regards,
Vijay Vanamala.
Re: GETTING NULLS WHEN USING PIVOT [message #653933 is a reply to message #653931] Fri, 22 July 2016 07:33 Go to previous messageGo to next message
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')
);
Re: GETTING NULLS WHEN USING PIVOT [message #653936 is a reply to message #653933] Fri, 22 July 2016 09:42 Go to previous messageGo to next message
Michel Cadot
Messages: 67949
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I prefer "extract(year from date_column)" (which is a number) to "to_char(DATE_COLUMN,'YYYY')" (which is a string).

Re: GETTING NULLS WHEN USING PIVOT [message #653939 is a reply to message #653894] Fri, 22 July 2016 10:24 Go to previous message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
I totally agree. If the year column in your table is a number use extract. If it is a string use to_char. Nice catch Michel.
Previous Topic: Deadlock experienced but Blocking lock expected
Next Topic: SQL query for formatted data display
Goto Forum:
  


Current Time: Fri Sep 24 05:26:36 CDT 2021