## Forum Stats

• 3,871,978 Users
• 2,266,360 Discussions

Discussions

# Help Using the PIVOT Function

2»

• Member Posts: 4,299 Gold Trophy
edited Mar 26, 2017 5:35PM
`mathguy wrote:jaramill wrote:FriendlyTooo wrote:Thanks for trying to help folks. I tried reading the links from John but for whatever reason my brain is stuck.Nonsense. The link is to post that describes HOW you should ask ANY question on these forums? It wasn't specific to your question but for all questions.[...]Not really. Please read Reply 1 again (to which the OP was referring). Then edit your Reply 9 as appropriate. Cheers, mathguy`

OOPs!  that font is REALLY ...tiny.  I thought it was the one for asking a question.  I'll update the my reply.

• Member Posts: 60
edited Mar 28, 2017 7:54PM

Without rounding

`with precipitation as (select trunc(sysdate - (1 + rownum)) rain_date,               case when SYS.dbms_random.value(0, 10) > 8                    then SYS.dbms_random.value(0, 2)                    else 0                end as inches          from dual connect by level <= 1000)select *  from (select to_char(rain_date,'MON') the_month,               inches          from precipitation         where rain_date >= trunc(sysdate, 'YEAR') ) pivot (avg(inches) for the_month in ('JAN' as jan,                                      'FEB' as feb,                                      'MAR' as mar,                                      'APR' as apr,                                      'MAY' as may,                                      'JUN' as jun,                                      'JUL' as jul,                                      'AUG' as aug,                                      'SEP' as sep,                                      'OCT' as oct,                                      'NOV' as nov,                                      'DEC' as dec));`

With rounding

`with precipitation as (select trunc(sysdate - (1 + rownum)) rain_date,               case when SYS.dbms_random.value(0, 10) > 8                    then SYS.dbms_random.value(0, 2)                    else 0                end as inches          from dual connect by level <= 1000)select *  from (select to_char(rain_date,'MON') the_month,               round(avg(inches), 4) avg_inches          from precipitation         where rain_date >= trunc(sysdate, 'YEAR')         group by to_char(rain_date,'MON')         ) pivot (max(avg_inches) for the_month in ('JAN' as jan,                                          'FEB' as feb,                                          'MAR' as mar,                                          'APR' as apr,                                          'MAY' as may,                                          'JUN' as jun,                                          'JUL' as jul,                                          'AUG' as aug,                                          'SEP' as sep,                                          'OCT' as oct,                                          'NOV' as nov,                                          'DEC' as dec));`
This discussion has been closed.