Pivot- Oracle 11g feature

Learn how to converts rows into columns using SQL pivot with Example queries.

The PIVOT operator takes data in separate rows, aggregates it and converts it into columns.

Syntax

SELECT …
FROM …
PIVOT [XML]
( pivot_clause
pivot_for_clause
pivot_in_clause )
WHERE …

pivot_clause: defines the columns to be aggregated (pivot is an aggregate operation)
pivot_for_clause: defines the columns to be grouped and pivoted;
pivot_in_clause: defines the filter for the column(s) in the pivot_for_clause (i.e. the range of values to limit the results to). The

aggregations for each value in the pivot_in_clause will be transposed into a separate column (where appropriate).

A simple example

we will display the no of employees in a separate column for each department, as follows.

SELECT *
FROM (
      SELECT DEPTNO 
      FROM EMP
     ) 
PIVOT
   (
    COUNT(*) as detp_employees_cnt FOR 
    DEPTNO IN(10, 20, 30)
   );

In the above example query
COUNT(*) is pivot_clause which performs aggregate operation.
DEPTNO is pivot_for_clause which performs group by operation.
10, 20, 30 range of values that transposed into a separate column.

group by operation performs based on the values in pivot_in_clause.

Output

Simple Example 2

we will display the Total salary of employees in a separate column for each department, as follows.

SELECT *
FROM
(
SELECT DEPTNO,
SAL
FROM EMP
)
PIVOT
(SUM(SAL) DEPT_TOTAL_SALARY
FOR DEPTNO IN (10,20,30)
);

Output

Simple Example 3

SELECT * 
FROM 
   (
    SELECT DEPTNO,
          /* JOB,*/
           SAL 
    FROM EMP
   )
PIVOT
    (SUM(SAL) DEPT_TOTAL_SALARY,
    count(SAL) DEP_SAL_CNT
     FOR DEPTNO IN (10,20,30)
    )  

Output

Simple Example 4

SELECT * 
FROM 
   (
    SELECT DEPTNO,
           JOB,
           SAL 
    FROM EMP
   )
PIVOT
    (SUM(SAL) DEPT_SUM,
    count(SAL) CNT
     FOR (DEPTNO,job) IN ((10,'SALESMAN'),(10,'CLERK'),(10,'MANAGER'))
    )   

Output

Simple Example 5

SELECT * 
FROM 
   (
    SELECT DEPTNO,EXTRACT( YEAR FROM TO_DATE(HIREDATE,'DD-MM-RRRR')) HIREDATE
    FROM EMP
   )
PIVOT
    (
    count(*) 
     FOR (HIREDATE) IN (1980 AS YEAR_1980,1981 AS YEAR_1981 ,1982 AS YEAR_1982,1983 AS YEAR_1983)
    )  

Output