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:
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.