PIVOT AND UNPIVOT IN 11g

Pivot

The Pivot clause is a new feature that has been introduced with the introduction of 11g.The simple logic behind the Pivot clause is that it enables the user to rotate rows into columns in the output of a query and at the same time enable the user to run aggregate functions on the data.

Unpivot

Unpivot clause is also a new builtin Analytical function that is introduced with the release of 11g,unpivot clause lets the user rotate columns into rows in the output from a query.


Examples to illustrate the use of Pivot clause

If you can log in to the Scott schema of the database then skip step 2 and 3 to execute the query directly

However , If you want to create the table in your database and then run the query please follow steps 1 through 3

1.Create table using the Script below

CREATE TABLE emp
( empno    NUMBER(4,0),
  ename    VARCHAR2(10 BYTE),
  job      VARCHAR2(9 BYTE),
  mgr      NUMBER(4,0),
  hiredate DATE,
  sal      NUMBER(7,2),
  comm     NUMBER(7,2),
  deptno   NUMBER(2,0)
); 

2.Insert Script

The script below can be used to insert data into the table created in step 1.

INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES ('7369','SMITH','CLERK','7902',TO_DATE('17-Dec-80','DD-MON-RR'),'800','0','30');

INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES ('7499','ALLEN','SALESMAN','7698',TO_DATE('20-Feb-81','DD-MON-RR'),'1600','300','30');

INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES ('7521','WARD','SALESMAN','7698',TO_DATE('22-Feb-81','DD-MON-RR'),'1250','500','30');

INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES ('7525','JACK','SALESMAN','7690',TO_DATE('26-Sep-81','DD-MON-RR'),'1550','500','20');

INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES ('7527','JONES','CLERK','7622',TO_DATE('22-Feb-81','DD-MON-RR'),'1000','300','20');

INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES ('7521','KANYE','PRESIDENT','',TO_DATE('28-Feb-81','DD-MON-RR'),'5000','500','20');

INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES ('7500','SAM','SALESMAN','7689',TO_DATE('23-Feb-81','DD-MON-RR'),'1257','500','10');

INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES ('7400','JOE','MANAGER','7607',TO_DATE('22-Feb-81','DD-MON-RR'),'1540','300','10');

INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES ('7521','WARD','SALESMAN','7698',TO_DATE('22-Feb-81','DD-MON-RR'),'1250','500','10');
COMMIT;
/

3.Pivot Query

SELECT *
FROM (SELECT deptno,sal
      FROM emp
      WHERE deptno > 0
)
PIVOT (SUM(sal) AS SUM_AMOUNT FOR (deptno) IN (10 AS ACCOUNTING ,20 AS RESEARCH,30 AS SALES,40 AS OPERATIONS));

/*
General Form of query using PIVOT clause
SELECT * FROM (INNER QUERY)
PIVOT (
AGGREGATE_FUNCTION
FOR (COLUMN TO BE PIVOTED) IN (LIST OF VALUES));
*/

A single Pivot clause also support multiple Aggregate Functions.
PIVOT CLAUSE provides great support to businesses which would like to do a detailed analysis on their Sales methodology and make future decisions on their sales and marketing strategies.

Using the UNPIVOT clause


The UNPIVOT clause rotates columns into rows.UNPIVOT clause is useful when a user has to execute a query that returns many columns , using the UNPIVOT clause the user can view those columns as rows.

Lets Illustrate the concept discussed above

1.Create a table pivot_emp_data using the query in step 3 as

CREATE TABLE pivot_emp_data AS
SELECT *
FROM (SELECT deptno,sal
              FROM emp
              WHERE deptno > 0
)
PIVOT (SUM(sal) AS SUM_AMOUNT
FOR (deptno)
IN (10 AS ACCOUNTING ,20 AS RESEARCH,30 AS SALES,40 AS OPERATIONS);

2.Once the table has been created we can run the query below

SELECT *
  FROM pivot_emp_data
  UNPIVOT
(sal FOR deptno
IN(ACCOUNTING_SUM_AMOUNT,RESEARCH_SUM_AMOUNT,SALES_SUM_AMOUNT,OPERATIONS_SUM_AMOUNT))

The Query rotates the pivoted Data .Also , do realize that If you are working with pivoted data, an UNPIVOT operation cannot reverse any aggregations that have been made by PIVOT or any other means.

Business Intelligence - Future

Few simple questions about the future of Business Intelligence.
1.What can truly change the implementation of Business Intelligence?
2.How fast can we adopt automated Decision support system?
3.How soon can we have real time data available in our datawarehouses?
4.Which methods can be implemented to stream line data across various Business Processes within an Enterprise?
5.Best set of tools that can be used to implement a sound BI solution.

These are just some questions that raced my mind today as I thought about the future of BI solutions.