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.
Let’s take the Human Resources (HR) schema, retrieve some data for analysis, and create a pivot table to see its benefits.
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:
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.
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: