Pivot Table

In 2006, I have written two blog entries about pivoting(rows-to-columns) one of them with aggregate functions the other one is with model clause. With 11G, Oracle has introduced a new feature about pivoting. This feature is pure SQL and is easy to use. Especially, pivoting is used in data warehouse reporting issues. It is also useful for measuring some daily transactions. Suppose that you have a transaction table and you want to know, summary report for transactions. Following pivot usage is demonstrated for this purpose:


Connected to Oracle Database 11g Express Edition Release 11.2.0.2.0 
Connected as hr@xE.LOCAL

SQL> 
SQL> drop table transaction_table;

Table dropped
SQL> create table transaction_table( tid  number, action_name varchar2(32), execution_time timestamp, execution_duration_ms number );

Table created
SQL> insert into transaction_table(tid, action_name,execution_time,execution_duration_ms) values(1,'SetContractStatus', systimestamp, 934);

1 row inserted
SQL> insert into transaction_table(tid, action_name,execution_time,execution_duration_ms) values(2,'SetContractStatus', systimestamp + 1, 810);

1 row inserted
SQL> insert into transaction_table(tid, action_name,execution_time,execution_duration_ms) values(3,'SetContractStatus', systimestamp + 1, 1098);

1 row inserted
SQL> insert into transaction_table(tid, action_name,execution_time,execution_duration_ms) values(4,'SetContractStatus', systimestamp, 978);

1 row inserted
SQL> insert into transaction_table(tid, action_name,execution_time,execution_duration_ms) values(5,'SetTariffModel', systimestamp, 691);

1 row inserted
SQL> insert into transaction_table(tid, action_name,execution_time,execution_duration_ms) values(6,'SetTariffModel', systimestamp+ 1, 801);

1 row inserted
SQL> insert into transaction_table(tid, action_name,execution_time,execution_duration_ms) values(7,'SetTariffModel', systimestamp, 587);

1 row inserted
SQL> insert into transaction_table(tid, action_name,execution_time,execution_duration_ms) values(8,'SetTariffModel', systimestamp, 762);

1 row inserted
SQL> insert into transaction_table(tid, action_name,execution_time,execution_duration_ms) values(9,'SetContractService', systimestamp, 450);

1 row inserted
SQL> insert into transaction_table(tid, action_name,execution_time,execution_duration_ms) values(10,'SetContractService', systimestamp, 507);

1 row inserted
SQL> commit;

Commit complete
SQL> select * from transaction_table;

       TID ACTION_NAME                      EXECUTION_TIME                                                                   EXECUTION_DURATION_MS
---------- -------------------------------- -------------------------------------------------------------------------------- ---------------------
         1 SetContractStatus                03/03/2013 23:04:08,789000                                                                         934
         2 SetContractStatus                04/03/2013 23:04:08,000000                                                                         810
         3 SetContractStatus                04/03/2013 23:04:08,000000                                                                        1098
         4 SetContractStatus                03/03/2013 23:04:08,869000                                                                         978
         5 SetTariffModel                   03/03/2013 23:04:08,903000                                                                         691
         6 SetTariffModel                   04/03/2013 23:04:08,000000                                                                         801
         7 SetTariffModel                   03/03/2013 23:04:08,954000                                                                         587
         8 SetTariffModel                   03/03/2013 23:04:08,974000                                                                         762
         9 SetContractService               03/03/2013 23:04:08,996000                                                                         450
        10 SetContractService               03/03/2013 23:04:09,028000                                                                         507

10 rows selected
SQL> select * from
  2      (select trunc(execution_time) as day, action_name, execution_duration_ms from transaction_table )
  3    pivot (
  4       avg(execution_duration_ms) as avg_exec,
  5       count(*)  as cnt
  6         for action_name in (
  7                                'SetContractStatus' as contract,
  8                                'SetTariffModel'   as tariff,
  9                                'SetContractService' as service
 10                             )
 11      );

DAY         CONTRACT_AVG_EXEC CONTRACT_CNT TARIFF_AVG_EXEC TARIFF_CNT SERVICE_AVG_EXEC SERVICE_CNT
----------- ----------------- ------------ --------------- ---------- ---------------- -----------
03/03/2013                956            2             680          3            478,5           2
04/03/2013                954            2             801          1                            0

SQL>