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

I realized I left out the database version; I am running this query on
Oracle 10g 10.2.0.3.0

also to add a bit more clarification, the first query above is
something simple like:

SELECT month, agent, cases_worked FROM datatable ORDER BY month ASC

On Dec 27, 3:27*pm, master44 <trp... (AT) gmail (DOT) com> wrote:
http://lmgtfy.com/?q=pivot+query+sit...tom.oracle.com

jg
--
@home.com is bogus.
http://www.computerweekly.com/Articl...and-Oracle.htm
hmmmmmm....

On Dec 27, 6:39*pm, joel garry <joel-ga... (AT) home (DOT) com> wrote:
Thanks, I have spent many hours with different Google searches and
browsing the AskTom site, still not real clear how to make this work.

On Dec 28, 3:59*pm, master44 <trp... (AT) gmail (DOT) com> wrote:
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
/

On Dec 27, 6:27*pm, master44 <trp... (AT) gmail (DOT) com> wrote:
If you follow Joel's links the second thead identifies that per the
SQL standard that the number of columns must be known at parse time.
To have both a variable number of columns and of agents would require
dynamic SQL built at run time. However, since there are only 12
months in a year if you can display by month or a set number of months
from a reference point (i.e., last 6 months) thns using the techniques
shown on this thread by another poster or found via the link you can
do what you asked.

If you cannot figure out how, then post DDL and DML to build a sample
set of data along with your attempt at the problem and maybe someone
will post the actual working SQL for you.

HTH -- Mark D Powell --