Re: oracle row months in columns

From: onedbguru <onedbguru_at_yahoo.com>
Date: Wed, 29 Dec 2010 19:00:06 -0800 (PST)
Message-ID: <4061de60-a69a-4774-a82d-6076973821c1_at_l17g2000yqe.googlegroups.com>

On Dec 28, 3:59 pm, master44 <trp..._at_gmail.com> wrote:
> On Dec 27, 6:39 pm, joel garry <joel-ga..._at_home.com> wrote:
>
>
>
>
>
>
>
>
>
> > On Dec 27, 3:27 pm, master44 <trp..._at_gmail.com> wrote:
>
> > > I have a query that returns results like this:
>
> > > Month           Agent     Cases Worked
> > > ----------------   -----------   -----------------------
> > > Sept              Tom            5
> > > Sept              John           12
> > > Sept              Megan         6
> > > Oct               Tom             2
> > > Oct               John            7
> > > Oct               Megan         8
> > > Nov               Tom            11
> > > Nov               John           13
> > > Nov               Megan        16
>
> > > I am looking to plot this data as a multiple line graph, and would
> > > like to change the output to look something like this, but am not
> > > having any luck:
>
> > > Agent     Sept     Oct     Nov
> > > ----------    -------    -------   -------
> > > Tom          5          2        11
> > > John         12         7        13
> > > Megan      6           8        16
>
> > > This is just a sample output, and the number of months returned or
> > > number of agents returned is variable, so for example above we are
> > > showing 3 months and 3 agents, but the result set could have 12 months
> > > and 5 agents as an example so the query would need to be dynamic to
> > > accomidate.
>
> > > Thanks
>
> >http://lmgtfy.com/?q=pivot+query+site%3Aasktom.oracle.com
>
> > jg
> > --
> > _at_home.com is bogus.http://www.computerweekly.com/Articles/2010/12/27/244642/CW+-Analyst3...
> > hmmmmmm....- Hide quoted text -
>
> > - Show quoted text -
>
> Thanks, I have spent many hours with different Google searches and
> browsing the AskTom site, still not real clear how to make this work.

Replacing your col/table names in this example doesn't work?

select job,

           max( decode( deptno, 10, cnt, null ) ) dept_10,
           max( decode( deptno, 20, cnt, null ) ) dept_20,
           max( decode( deptno, 30, cnt, null ) ) dept_30,
           max( decode( deptno, 40, cnt, null ) ) dept_40.
      from ( select job, deptno, count(*) cnt
               from emp
              group by job, deptno )
     group by job

  /

Something like:

select agent,

           max( decode( month, 'Jan', cnt, null ) ) Jan,
           max( decode( month, 'Feb', cnt, null ) ) Feb,
           max( decode( month, 'Mar', cnt, null ) ) Mar,
           max( decode( month, 'Apr', cnt, null ) ) Apr
--... etc...
      from ( select agent, month, count(*) cnt
               from yourtable
              group by agent, month )
     group by agent

  / Received on Wed Dec 29 2010 - 21:00:06 CST