Wednesday 22 May 2013

Analytic functions within Pivot statements

Today I found that SQL analytics go hand in hand with PIVOT statements.

I had percentage data pivoted by a certain column, but I needed to sort it by
the total across each row.

For the purpose of this demo I create a table with some random monthly data
create table my_data as 
select /*+ no_merge */ username, round(dbms_random.value(1,12)) mth, round(dbms_random.value(1,100),0) val 
from my_users connect by level < 4;
Now with the following query I transpose the monthly totals across as columns using the PIVOT.
select * from (
  select  sum(val) av, username, mth
     ,sum(sum(val)) over (partition by username order by null) tot
  from my_data
  group by username, mth
) 
pivot 
(avg(av) -- avg() because I didn't need to modify the value any further
         -- I'm open to other suggestions
  for mth in 
  (1  as jan
  ,2  as feb
  ,3  as mar
  ,4  as apr
  ,5  as may
  ,6  as jun
  ,7  as jul
  ,8  as aug
  ,9  as sep
  ,10 as oct
  ,11 as nov
  ,12 as dec
  )
)
order by tot desc
Note the highlighted line - an analytical statement that sums yearly total for each username. This was the simplest way I could determine
a) a way to determine that total
b) order the report by the user with the highest totals

I haven't used PIVOT queries too often yet, but I can see how analytical functions will be vital to providing, er, pivotal information.