11g: SQL Pivot

Just wanted to post a quick example of the new 11g SQL Pivot syntax (documented here).

Here’s the old pivot method (I believe Tom Kyte invented this one):

select sum("10") "10",sum("20") "20",
       sum("30") "30",sum("40") "40",
	   sum("50") "50",sum("60") "60",
	   sum("70") "70",sum("80") "80",
	   sum("90") "90",sum("100") "100",
	   sum("110") "110"
  from(
select max(decode(department_id,10,salary,null)) "10",
       max(decode(department_id,20,salary,null)) "20",
       max(decode(department_id,30,salary,null)) "30",
	   max(decode(department_id,40,salary,null)) "40",
       max(decode(department_id,50,salary,null)) "50",
       max(decode(department_id,60,salary,null)) "60",
	   max(decode(department_id,70,salary,null)) "70",
	   max(decode(department_id,80,salary,null)) "80",
	   max(decode(department_id,90,salary,null)) "90",
       max(decode(department_id,100,salary,null)) "100",
	   max(decode(department_id,110,salary,null)) "110"
  from employees
  group by department_id);

It’s a very effective method that I’ve used many times, but the syntax is a bit clunky.

And now, the 11g Pivot syntax:

select *
  from (select department_id,sum(salary) salary
          from employees
         where department_id > 0
         group by department_id)
  pivot (sum(salary) 
    for department_id in (10,20,30,40,50,60,70,80,90,100,110));



10     20     30     40     50     60     70     80      90     100    110   
------ ------ ------ ------ ------ ------ ------ ------- ------ ------ ------
4400   19000  24900  6500   156400 28800  10000  319500  58000  51600  20300 

1 rows selected