Add New Skills!

Udemy
Showing posts with label Oracle Pivot and Unpivot. Show all posts
Showing posts with label Oracle Pivot and Unpivot. Show all posts

Sunday, November 20, 2011

Oracle 11g Pivot and Unpivot Operators

 

The pivot_clause of the SELECT statement lets you write cross-tabulation queries that rotate rows into columns, aggregating data in the process of the rotation. Pivoting is a key technique in data warehouses.

Syntax:
SELECT .... FROM <table-expr>
PIVOT (
       aggregate-function(<column>)
       FOR <pivot-column>
       IN
       (<value1>, <value2>,..., <valuen>)
         ) AS <alias>
WHERE .....

examples:

-- PIVOT 1
SELECT *
  FROM (
            SELECT DEPARTMENT_ID, HML_SALARY_IND, SALARY FROM EMPLOYEES2            
            ) A
  PIVOT(
            SUM(SALARY)
             FOR (HML_SALARY_IND)
             IN ('H' AS HIGH, 'M' AS MEDIUM, 'L' AS LOW)
            )

image

--PIVOT2
SELECT *
FROM (
          SELECT DEPARTMENT_ID, HML_SALARY_IND, SALARY FROM EMPLOYEES2            
          ) A
PIVOT(
          SUM(SALARY) AS SUM_SAL,
          AVG(SALARY) AS AVG_SAL
          FOR (HML_SALARY_IND)
           IN ('H' AS HIGH, 'M' AS MEDIUM, 'L' AS LOW, '*' AS UNKNOWN)
          )

image

changing Department ID to Department Name:

--PIVOT 3           
  SELECT *
  FROM (
            SELECT D.DEPARTMENT_NAME, HML_SALARY_IND, SALARY
            FROM EMPLOYEES2 E, DEPARTMENTS D
            WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID            
            ) A
  PIVOT(
            SUM(SALARY) AS SUM_SAL,
            AVG(SALARY) AS AVG_SAL
            FOR (HML_SALARY_IND)
             IN ('H' AS HIGH, 'M' AS MEDIUM, 'L' AS LOW, '*' AS UNKNOWN)
            )

image

Unpivot Operator

An unpivot does not reverse a PIVOT operation. Instead, it rotates data from columns into rows. If you are working with pivoted data, an UNPIVOT operation cannot reverse any aggregations that have been made by PIVOT or any other means.

--create a pivot table

CREATE TABLE PIVOT1 AS
  SELECT *
  FROM (
            SELECT DEPARTMENT_ID, HML_SALARY_IND, SALARY FROM EMPLOYEES2            
            ) A
  PIVOT(
            SUM(SALARY)
             FOR (HML_SALARY_IND)
             IN ('H' AS HIGH, 'M' AS MEDIUM, 'L' AS LOW)
            )

SELECT * FROM PIVOT1

image

--UNPIVOT1
SELECT * FROM PIVOT1
UNPIVOT INCLUDE NULLS
(
SUM_SAL FOR HML_SALARY_IND IN(HIGH AS 'H', MEDIUM AS 'M' , LOW AS 'L')
)

image

--UNPIVOT EXLUDE NULLS
SELECT * FROM PIVOT1
UNPIVOT
(
SUM_SAL FOR HML_SALARY_IND IN(HIGH AS 'H', MEDIUM AS 'M' , LOW AS 'L')
)

image