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 built in 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 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 can 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 help them 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.
·
Session Abstract: 