SQL - PIVOT clause Series - Basics

PIVOT is a tricky clause in SQL. One way it is easy if it is completely understood. It is very difficult, if you don’t know how it works. This post starts with basics of PIVOT clause with examples.

Basically, PIVOT converts a row data into column data. Let’s understand what that means with an example.

Assume a table as shown below. Scripts to create table structure and sample data are given here.

Now, let’s assume you are CEO and you want to analyze the current budgets for each department and for each role that you are currently investing to run company operations.

A simple select will give a data as shown in this screenshot.

SELECT department, role, annual_salary

FROM employees

WHERE department IS NOT NULL

This data makes sense but this is not it, as a CEO you are interested more in comparing the data like, the budget for SVP's in Sales department and Computer Services, Total salesman’s budget etc…

In short, the following summary type of view makes more sense than just the raw data (Or data as it is). Following screenshot has sum of salaries listed for each department and for each role. Columns with empty fields means there is no role in that department.

Budget comparison is much easier this way, isn't it?

This can be easily done using PIVOT clause, here is the SQL Code.

SQL Code:

SELECT *
FROM
(     /* Base query */
      SELECT department, role, annual_salary
      FROM employees
      WHERE department IS NOT NULL
)
PIVOT
(
  SUM(annual_salary) AS total_budget
  FOR(department)
  IN ('Computer Services' AS computer_services,
      'Human Resource Management' AS hr,
      'Sales' AS sales
     )
)
ORDER BY role

Read more on PIVOT @