PIVOT and UNPIVOT Feature with 11g

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
PIVOT (
       aggregate-function()
       FOR
       IN
       (, ,..., )
         ) AS
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)
            )

--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)
          )

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)
            )

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

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

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