What is Pivot Table
Data analysis is not always a snap. You can look at the same data from different angles and only some of them can bring valuable information. When dealing with data, one often beats his brains with grouping, sorting, or doing anything else to get required results and grasp the whole picture. Pivot Table is designed exactly to end these miseries and enable you to fisk any obscure data with several mouse clicks. It’s possible to be a data-management guru with Pivot Table.
Pivot Table is a tool that converts large amounts of data into compact and informative summaries - pivot tables. You can rearrange (or pivot) your data by a simple drag of a mouse until you get the layout best for understanding the data relations and dependencies.
Pivot Table in Use
Let’s take the Human Resources (HR) schema, retrieve some data for analysis, and create a pivot table to see its benefits.
Note
As the original HR database contains a huge number of table records, they were cut down to half to illustrate the example.
Suppose we need to find out the salary volume of each employee from all departments sorted by regions. The main data is stored in the Employees table, other tables are required to get full names of film categories and film titles.
To get required data from the tables, you should select the following fields and build a query using Query Builder:
- JOB_TITLE from the JOBS table
- EMPLOYEE_ID, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID from the EMPLOYEES table
- DEPARTMENT_NAME and LOCATION_ID from the DEPARTMENTS table
- CITY, STATE_RPOVINCE and COUNTRY_ID from the LOCATIONS table
- COUNTRY_NAME from the COUNTRIES table
- REGION_NAME from the REGIONS table
This is what you should have received after executing the query. The usual grid will display the data the same way.
As you see, the data is large and can hardly be analyzed. Let’s see how data can be displayed in a pivot table. To create a pivot table, you need to open the Pivot Table view of the SQL document and drag the required fields from the Data Source window (which opens automatically with the list of fields specified in the query) into required areas in a pivot table. The Pivot Table view contains the pivot table areas, so it’s easy to understand how the dragged fields will be displayed.
Now, following the tips, let’s drag salary into the Data area, and region, country, city, department, job and employee name name to the Row area.
Note
Pivot Grid automatically decomposes any fields of date or time type, so you can see payment date field decomposed into year, month, and day fields.
Now you have the following neat summaries:
- Salary for each employee
- Total salary in each department
- Total salary in each region
- Grand Total salary value
