Re: oracle row months in columns
Date: Thu, 30 Dec 2010 10:57:49 -0800 (PST)
Message-ID: <470e0b21-01ce-43bf-a233-51411cf7e160_at_d8g2000yqf.googlegroups.com>
On Dec 27, 6: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
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 -- Received on Thu Dec 30 2010 - 12:57:49 CST