Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Help Using the PIVOT Function

FriendlyToooMar 24 2017 — edited Mar 28 2017

Hi Folks:

Background:

We have (currently) 10 rain gauges.  I am using the following SQL code to group the AVG of the gauges per month for the current year:

  SELECT TO_CHAR (RAIN_DATE, 'MM/YYYY'), AVG (PRECIPITATION_AMOUNT)
    FROM WEATHER.PRECIPITATION
GROUP BY TO_CHAR (RAIN_DATE, 'YYYY'), TO_CHAR (RAIN_DATE, 'MM/YYYY')
  HAVING TO_CHAR (RAIN_DATE, 'YYYY') = TO_CHAR (SYSDATE, 'YYYY')
ORDER BY TO_DATE (TO_CHAR (RAIN_DATE, 'MM/YYYY'), 'MM/YYYY') DESC;

An example of the current output would be:

04/2017    6.465

03/2017    .496363636

So for example March had an average of almost .5 inches and (fake data) April almost 6.5 inches.

What I need to be able to do for a report is to make the month(s) into columns and the  AVG amount of rain into the row  (so there will always only be one row of data that contains the avg amount of rain. Each month a new average is added.

Any help on how to use the pivot function would be greatly appreciated.

Thanks,

Matthew

Comments

Jose Cruz

Excellent Article!!!

user12184782

With all due respect, Lambda expression is like a Goto statement:

Cute workaround/semi-patch for poor planning.

Having one or two Lambda expressions in piece of code might sound reasonable, at first glance. Same is with goto-statements. And if the code is yours, you are pretty sure what is going on.

In case there are a lot of Lambda expressions, number of line-codes is decreased, by having more statements in one single line. This might lead to decreased readability and maintainability of code - which is quite the opposite of initial idea.

(Concerning Lambda expression, I'm starting to have a feeling that Java is being polluted by bad ideas, recycled from C#.)

C used to be famous for its poor/chaotic readability (multiple statements in one line, etc). If we want to adopt mono-line coding style (multiple statements in one line), then we might go back one step (I don't think this can be named 'progress').

I read the article, and I respect the effort and the idea. However, I'm not convinced, that Lambda expression is good. Proper architectural planning and modeling should/must be made, in order to avoid any necessity for Lambda expression.

PraveenM90

wah, Excellent Article is i felt lambda expressions too hard in beginning after few work am getting its OK but not clear. after reading this article Completely clear crystal clear sharp explanation

Lambda expressions using is good for one or two instances yes its too worse when enter into maintenance but some times it will make so much impotence suppose if we take swing components or Android development event handling those are very clearly handled by IDE like studio its good idea but at the same time also....

Thanks for this Article..

Praveen :-)

user13279423

seems that the formatting in the code is incorrect such as: Listing 9. Java clienti. The code is just displayed on a single line which is mostly unreadable.

Mohan Basavarajappa

seems that the formatting in the code is incorrect such as: Listing 9. Java clienti. The code is just displayed on a single line which is mostly unreadable.

hi

thanks for pointing out. will check with moderator whether we can take care of formatting so that it's readable.

Mohan Basavarajappa

With all due respect, Lambda expression is like a Goto statement:

Cute workaround/semi-patch for poor planning.

Having one or two Lambda expressions in piece of code might sound reasonable, at first glance. Same is with goto-statements. And if the code is yours, you are pretty sure what is going on.

In case there are a lot of Lambda expressions, number of line-codes is decreased, by having more statements in one single line. This might lead to decreased readability and maintainability of code - which is quite the opposite of initial idea.

(Concerning Lambda expression, I'm starting to have a feeling that Java is being polluted by bad ideas, recycled from C#.)

C used to be famous for its poor/chaotic readability (multiple statements in one line, etc). If we want to adopt mono-line coding style (multiple statements in one line), then we might go back one step (I don't think this can be named 'progress').

I read the article, and I respect the effort and the idea. However, I'm not convinced, that Lambda expression is good. Proper architectural planning and modeling should/must be made, in order to avoid any necessity for Lambda expression.

hi

appreciate your point of view. functional programming idioms are there for quite sometime. java was late in joining the party and took time for incorporating it as language feature. there were lot of discussions on this whether or not to include.

This article is to understand the nuances, appreciate good aspects and avoid bad patterns. Just because a language feature is available, force-fitting it to all context and usage will surely lead of bad patterns what you've highlighted. Agree to your point that planning and architectural aspects have to be in-place and just language features alone can't solve all the problem.

Usage of any feature should be based on fitment rather than compulsion. This might not be good for all cases or scenario's but might serve well for specific cases

We are in the process of fixing the code format. Unfortunately, we need more time. Yolande

jwenting

hi

thanks for pointing out. will check with moderator whether we can take care of formatting so that it's readable.

The problem is that the ( i ) is being rendered by the html generator as a symbol. Putting spaces around the "i" should hopefully prevent that.

jwenting

With all due respect, Lambda expression is like a Goto statement:

Cute workaround/semi-patch for poor planning.

Having one or two Lambda expressions in piece of code might sound reasonable, at first glance. Same is with goto-statements. And if the code is yours, you are pretty sure what is going on.

In case there are a lot of Lambda expressions, number of line-codes is decreased, by having more statements in one single line. This might lead to decreased readability and maintainability of code - which is quite the opposite of initial idea.

(Concerning Lambda expression, I'm starting to have a feeling that Java is being polluted by bad ideas, recycled from C#.)

C used to be famous for its poor/chaotic readability (multiple statements in one line, etc). If we want to adopt mono-line coding style (multiple statements in one line), then we might go back one step (I don't think this can be named 'progress').

I read the article, and I respect the effort and the idea. However, I'm not convinced, that Lambda expression is good. Proper architectural planning and modeling should/must be made, in order to avoid any necessity for Lambda expression.

Yes, I have the same apprehensions about lambdas in Java. While a nice idea in some scenarios (like replacing anonymous inner classes) overall it feels wrong, a way for overly smart people to show how smart they are by writing highly obfuscated code, exactly the thing that Java was originally intended to make harder (in part by forcing a somewhat more verbose way of writing code than e.g. C does).

Mohan Basavarajappa

The problem is that the ( i ) is being rendered by the html generator as a symbol. Putting spaces around the "i" should hopefully prevent that.

it happened due to some migration which oracle did few months back. this site went through some migration stuff and most of content had these issues is what i understand from community manager. hope they fix it!

Kashif Sohail

I was using Lambda Expressions in C#, while I switched to java, and feeling very happy to see it in java too.

unknown-3341152

Lambda is great, but Java is still cumbersome. I made a library to make lambda more useful:

http://wp.me/p7lGfP-1u

Have a look!

1 - 12
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 25 2017
Added on Mar 24 2017
12 comments
920 views