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