Tyler Muth’s Blog

Technology with a focus on Oracle, Application Express and Linux

11g: SQL Pivot

Posted by Tyler Muth on September 18, 2007

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

7 Responses to “11g: SQL Pivot”

  1. jiri said

    I saw at least 10 web sites talking about 11g features …. I did not see this before. To be honest, this is NUMBER ONE for all data warehouse analysts. It’s so simple to create transposed query using this, I cannot believe they did not do it before.

    There was only one feature I liked on MS ACCESS for years, it was transposed queries. Finally I can get it in ORACLE.

    thank you for this post
    jiri

  2. seshu36 said

    GOOD

  3. JonB said

    Is an excellent feature.
    What would make it better is if the IN statement could contain another SELECT rather than just literals.

  4. Tyler Muth said

    If the IN statement were a select instead of literals, then the columns could not be determined at parse time. This would implicitly make the statement Dynamic SQL. However, you could explicitly write this as Dynamic SQL.

  5. Lawrence Kelly said

    It’s definately nice to have, but Oracle weren’t in any rush as I am sure users could simply spool to excel pivot, unpivot and reload, so easily. I built some cool dashboards with Apex and it does it all, ad hoc web based views of business wharehouse, user log in and password etc and you can dump to excel or any file type etc.

    Plus there are other ways to do it as Tom described so…..

    But yeah nice to have it🙂

  6. Jorge Rafael said

    I’m trying to use the pivot function by this way but is not working can you help me please?
    SELECT *
    FROM (SELECT to_char(fecha,’DD/MM/YYYY’) as fecha, buffer
    FROM “Buffer_reporte”)
    PIVOT (SUM(buffer) AS Buffer FOR (fecha) IN
    (SELECT DISTINCT to_char(fecha,’DD/MM/YYYY’) from “Buffer_reporte”));

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: