turning columns into rows

[davey23uk]

Got this query (9.2.0.5)

select avg(cpu_used) from system_capacity_cpu 
where to_char(metric_date,'MM/YYYY') = to_char(to_date('01/02/2007','DD/MM/YYYY'),'MM/YYYY') and hostname = 'xxx'

What that tables contains is a hostname, a month and cpu usage,

what I need to get is the output for each Month listed in the table (in the metric_date column) on the same line, so the output would be

hostname      01/2007      02/2007     03/2007      
xxx              1.2             1.3            1.4

Anyone know how to do that?

[gandolf989]

You are talking about a pivot query. You can get it for all of the hostnames of you can limit to just one hostname.

SELECT *
  FROM ( SELECT hostname, AVG(cpu_used) Jan2007
           FROM system_capacity_cpu 
          WHERE TRUNC(metric_date,'MM') = TO_DATE('01/02/2007','DD/MM/YYYY')
          GROUP BY hostname ) Jan2007
 INNER JOIN
       ( SELECT hostname, AVG(cpu_used) Feb2007
           FROM system_capacity_cpu 
          WHERE TRUNC(metric_date,'MM') = TO_DATE('01/02/2007','DD/MM/YYYY')
          GROUP BY hostname ) Feb2007
    ON Jan2007.Jan2007 = Feb2007.Feb2007
 INNER JOIN
       ( SELECT hostname, AVG(cpu_used) Mar2007
           FROM system_capacity_cpu 
          WHERE TRUNC(metric_date,'MM') = TO_DATE('01/02/2007','DD/MM/YYYY')
          GROUP BY hostname ) Mar2007
    ON Jan2007.Jan2007 = Mar2007.Mar2007
 ORDER BY hostname;

[davey23uk]

thanks! will try that out tomorrow when i am back in work

[cjard]

Uhh.. i think you need to JOIN ON the hostname, NOT the month...

I never knew a universe where Jan 2007 was = to Feb 2007

But other than that.. It's okay.

Actually you can do this another way that only needs one table scan:

SELECT
  hostname,
  AVG(CASE WHEN TRUNC(metric_date, 'MM') = Jan THEN cpu_used ELSE null END) as jan_avg,
  AVG(CASE WHEN TRUNC(metric_date, 'MM') = Feb THEN cpu_used ELSE null END) as feb_avg,
  AVG(CASE WHEN TRUNC(metric_date, 'MM') = Mar THEN cpu_used ELSE null END) as mar_avg

FROM
  system_capacity_cpu
  CROSS JOIN
  (
    SELECT 
      TO_DATE('01012007','DDMMYYYY') as Jan,
      TO_DATE('01022007','DDMMYYYY') as Feb,
      TO_DATE('01032007','DDMMYYYY') as Mar
    FROM dual
  ) date_constants

GROUP BY
  hostname

also gandolf I think your wuery has a cut n paste error because you repeatedly say a date of '01/02/2007' for jan and mar also.. but '01/02/2007' is feb...

[cjard]

For an expl of how my query works:

The date_constants is simply 3 rows of date constants that are added on via cross join. it makes the CASE WHEN a bit less messy.

Whole table is scanned (note you could and should add a WHERE clause so only JAN FEB MAR are returned as they are the only ones that will give any meaningful values)

The table is widened out virtually to columns that are either filled with cpu, or null depending on the date::

hostname, metric_date, cpu_used
abc.com, 01-jan-2007, 1.0
abc.com, 03-jan-2007, 2.0
abc.com, 01-feb-2007, 1.0
abc.com, 03-mar-2007, 2.0


become:

hostname, metric_date, cpu_used, jan, feb, mar
abc.com, 01-jan-2007, 1.0, 1.0, null, null
abc.com, 03-jan-2007, 2.0, 2.0, null, null
abc.com, 01-feb-2007, 1.0, null, 1.0, null
abc.com, 03-mar-2007, 2.0, null, null, 2.0


when AVG is worked out, any nulls are discounted. the whole thing collapses to a single line. Date is gone because we arent grouping by it:

hostname, cpu_used, janavg, febavg, maravg
abc.com, 1.0, 1.5, 1.0, 2.0




This exploits the fact that the average of (1, null, 2) is 1.5, not 1. If nulls behaved like 0 then it would be average of 1, but nulls dont partake in averages at all, so its 2+1 / 2, not 2+0+1/3

Savvy?