Oracle DBA

In reporting and analytics, we often need to rotate data — turning rows into columns or columns into rows.
SQL provides two powerful operators for this purpose: PIVOT and UNPIVOT.

• PIVOT transforms row values into columns, aggregating data.
• UNPIVOT does the reverse, turning columns into rows.

Let’s explore them step by step with examples from the HR schema.

Example 1: Pivoting Employee Count by Job and Department

This query counts employees by job_id and displays them as columns across departments.

SELECT *
FROM (
    SELECT department_id, job_id, employee_id
    FROM employees
) 
PIVOT (
    COUNT(employee_id) 
    FOR job_id IN ('IT_PROG' AS IT_Programmer, 
                   'ST_CLERK' AS Stock_Clerk,
                   'SA_REP' AS Sales_Rep)
) 
ORDER BY department_id;

Output:

DEPARTMENT_ID IT_PROGRAMMER STOCK_CLERK SALES_REP
------------- ------------- ----------- ---------
           10             2           0         0
           20             0           1         2
           30             0           3         4

Explanation:

• The inner query selects department, job, and employee.
• PIVOT rotates job IDs (IT_PROG, ST_CLERK, SA_REP) into columns.
• The result shows how many employees in each department hold these roles.

Example 2: Pivoting Average Salary by Department and Job

We calculate the average salary for each job_id within departments.

SELECT *
FROM (
    SELECT department_id, job_id, salary
    FROM employees
) 
PIVOT (
    AVG(salary) 
    FOR job_id IN ('IT_PROG' AS IT_Programmer, 
                   'SA_REP' AS Sales_Rep,
                   'FI_ACCOUNT' AS Finance_Accountant)
) 
ORDER BY department_id;

Output:

DEPARTMENT_ID IT_PROGRAMMER SALES_REP FINANCE_ACCOUNTANT
------------- ------------- --------- ------------------
           10         6000       5000               NULL
           20         NULL       6200               7000
           30         NULL       4500               NULL

Explanation:
Each department now has columns for average salary by job role, making it easier to compare compensation across functions.

Example 3: Pivoting Monthly Hires by Year

This query shows the number of hires per month for each year.

SELECT *
FROM (
    SELECT EXTRACT(YEAR FROM hire_date) AS hire_year,
           TO_CHAR(hire_date, 'MON') AS hire_month,
           employee_id
    FROM employees
) 
PIVOT (
    COUNT(employee_id) 
    FOR hire_month IN ('JAN' AS Jan, 'FEB' AS Feb, 'MAR' AS Mar,
                       'APR' AS Apr, 'MAY' AS May, 'JUN' AS Jun,
                       'JUL' AS Jul, 'AUG' AS Aug, 'SEP' AS Sep,
                       'OCT' AS Oct, 'NOV' AS Nov, 'DEC' AS Dec)
) 
ORDER BY hire_year;

Output:

HIRE_YEAR JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
--------- --- --- --- --- --- --- --- --- --- --- --- ---
     2020   1   2   0   3   0   1   0   0   0   2   0   1
     2021   0   1   1   0   2   0   3   1   0   1   0   0
     2022   2   0   2   0   1   2   0   1   1   0   2   1

Explanation:
By pivoting months into columns, HR can quickly analyze seasonal hiring patterns.

Example 4: Unpivoting Department Salary Averages for Analysis

We compute aggregates (avg, min, max) and then unpivot them into rows.

SELECT department_id, salary_type, avg_salary
FROM (
    SELECT department_id, 
           AVG(salary) AS avg_salary, 
           MIN(salary) AS min_salary, 
           MAX(salary) AS max_salary
    FROM employees
    GROUP BY department_id
) 
UNPIVOT (
    avg_salary FOR salary_type IN (avg_salary AS 'Average', 
                                   min_salary AS 'Minimum', 
                                   max_salary AS 'Maximum')
)
ORDER BY department_id;

Output:

DEPARTMENT_ID SALARY_TYPE AVG_SALARY
------------- ----------- ----------
           10 Average           5000
           10 Minimum           3000
           10 Maximum           7000
           20 Average           5750
           20 Minimum           4500
           20 Maximum           7000
           30 Average           4000
           30 Minimum           3500
           30 Maximum           4500

Explanation:
Instead of three separate columns, we now have one column salary_type, making it easier to compare metrics or plot them in BI tools.

Example 5: Unpivoting Employee Job History by Date

We transform job start and end dates into event rows.

SELECT employee_id, event_type, event_date
FROM (
    SELECT employee_id, start_date, end_date
    FROM job_history
) 
UNPIVOT (
    event_date FOR event_type IN (start_date AS 'Start', end_date AS 'End')
)
ORDER BY employee_id, event_date;

Output:

EMPLOYEE_ID EVENT_TYPE EVENT_DATE
----------- ---------- ----------
          1 Start      01-JAN-18
          1 End        31-DEC-19
          2 Start      15-MAR-19
          2 End        30-JUN-20
          3 Start      01-JUL-20
          3 End        30-SEP-21

Explanation:
By unpivoting, each change is shown as a timeline event, which is much easier for historical or career progression analysis.

Example 6: Pivoting Total Salary Expense by Location and Department

We summarize salary expenses by department across locations.

SELECT *
FROM (
    SELECT d.location_id, e.department_id, e.salary
    FROM employees e
    JOIN departments d ON e.department_id = d.department_id
) 
PIVOT (
    SUM(salary) 
    FOR department_id IN (10 AS Dept_10, 20 AS Dept_20, 30 AS Dept_30)
) 
ORDER BY location_id;

Output:

LOCATION_ID   DEPT_10   DEPT_20   DEPT_30
----------- --------- --------- ---------
       1000     15000     11500      NULL
       2000      NULL       7000      9000
       3000      NULL       NULL     12000

Explanation:
This produces a clear expense report by location and department, enabling finance teams to track costs geographically.

Key Takeaways

• PIVOT is ideal for cross-tab reports (departments vs jobs, year vs months, etc.).
• UNPIVOT is ideal for restructuring wide data into long format, useful for analytics.
• Both operators reduce the need for complex joins or exporting data to Excel for reshaping.

Together, they make SQL a powerful tool for business reporting, HR analytics, and financial dashboards.