Skip to Main Content

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
Post Details
Added on Mar 24 2017
12 comments
526 views