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 descNote 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.