SQL pivot tables: Understanding pivot tables in SQL Server

SQL pivot tables are a powerful and convenient way to summarize and analyze large data or records. They allow you to rearrange rows and columns in a dataset, group data together, and perform calculations on the grouped data.

In this article, We will try to learn what pivot tables are and how to use them in SQL.

sql-pivot-tables
SQL pivot tables

What are SQL pivot tables?

A pivot table is a summary of a larger dataset in an organized way so that you can quickly see the trends and patterns in the data. It rearranges the rows and columns of the dataset in such a way that makes it easy to understand and see how different variables relate to each other. Pivot tables are commonly used in spreadsheet software, such as Microsoft Excel, but they can also be created using SQL.

A SQL Pivot Table is specially designed for:

  • Aggregating and summarizing large amounts of data.
  • Rotating rows of data into columns and vice versa.
  • Easily creating cross-tabulations and summary reports.
  • Allowing for the creation of custom calculations and groupings.
  • Facilitating the comparison of data across multiple dimensions.
  • Generating interactive and dynamic reports.
  • Organizing and presenting data in a more intuitive and visual manner.

How can you create a pivot table in SQL?

You need to use the GROUP BY and CASE statements to create a pivot table in SQL Server. The GROUP BY statement groups the rows of the data set by a particular column or set of columns. The CASE statement allows you to specify different calculations for different groups of data.

Let’s create an employees table with four columns:

  • employee_id: a unique identifier for each employee.
  • job_title: the title of the employee’s job.
  • gender: the gender of the employee (M or F).
  • salary: the salary of the employee.
employee_idjob_titlegendersalary
1ManagerM75000
2SalespersonF50000
3EngineerM65000
4ReceptionistF30000
5AccountantM55000
employees table

Example:

The following is an example of how to create a pivot table in SQL that shows the average salary for each job title:

SELECT job_title,
       AVG(CASE WHEN gender = 'M' THEN salary ELSE NULL END) AS avg_male_salary,
       AVG(CASE WHEN gender = 'F' THEN salary ELSE NULL END) AS avg_female_salary
FROM employees
GROUP BY job_title

Here is how the records would look after running the above SQL SELECT statement:

job_titleavg_male_salaryavg_female_salary
Manager75000null
Salespersonnull50000
Engineer65000null
Receptionistnull30000
Accountant55000null
SQL Pivot table

Here we can see that the pivot table shows the average salary for each job title, with one column showing the average salary for males and another column showing the average salary for females. The corresponding column will show null if there are no males or females in a particular job title,

Pivot table examples

Let’s create a sales_data table with the following four columns:

  • product_id: a unique identifier for each product.
  • product_name: the name of the product.
  • year: the year in which the sales were made.
  • sales: the total sales for the product in that year.
product_idproduct_nameyearsales
1Product A202110000
2Product A202212000
3Product A203315000
4Product B20217000
5Product B20228000
6Product B20239000
7Product C20215000
8Product C20225500
9Product C20236000

Pivot table (Total sales by year and product)

This pivot table will display the total sales for each product, with one column showing the sales for each year.

Example:

SELECT product_name,
       SUM(CASE WHEN year = 2021 THEN sales ELSE NULL END) AS sales_2021,
       SUM(CASE WHEN year = 2022 THEN sales ELSE NULL END) AS sales_2022,
       SUM(CASE WHEN year = 2023 THEN sales ELSE NULL END) AS sales_2023
FROM sales_data
GROUP BY product_name
product_namesales_2021sales_2022sales_2023
Product A100001200015000
Product B700080009000
Product C500055006000
pivot table example

Here, We can see the pivot table sales_data shows the total sales for each product, with one column showing the sales for each year. If there are no sales for a particular product in a particular year, the corresponding column will display null value.

Conclusion

SQL pivot table is a useful tool for summarizing and analyzing large amounts of data. It allows you to rearrange rows and columns, group data together, and perform calculations on the grouped data. You can create pivot by using the GROUP BY and CASE statements.

Recommended articles:

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments