Forum Stats

  • 3,871,978 Users
  • 2,266,360 Discussions
  • 7,911,017 Comments

Discussions

Help Using the PIVOT Function

2»

Answers

  • jaramill
    jaramill 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.

  • JPDominator
    JPDominator 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.