Re: oracle row months in columns

From: Mark D Powell <Mark.Powell2_at_hp.com>
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

Original text of this message