SQL pivot tables: Understanding pivot tables in SQL Server

Last updated on January 14th, 2023 at 03:19 am

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.

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.

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:

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.

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

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:

• Types of Joins in SQL Server
• Function vs Stored procedure in SQL Server
• Stored Procedure in SQL Server With Examples
• Create, Alter, and Drop Database In SQL Server
• SQL Comparison Operators (Less than, Greater than, Equal, Not Equal operators)
• Primary Key Constraint In SQL Server
• View In SQL | Types Of Views In SQL Server
• SQL Server Trigger Update, Insert, Delete Examples

• Author
• Recent Posts

Shekh Ali

Meet Sheikh Ali, a skilled Software engineer and passionate a blogger. He enjoys sharing his expertise in C#, Java, SQL, Design Patterns, and other related topics through his writing. When he's not coding, Sheikh Ali can often indulge in his passions for reading books and fitness training.

Latest posts by Shekh Ali (see all)

• Difference Between Array And ArrayList In C#: Choosing the Right Collection - May 28, 2024
• C# Program to Capitalize the First Character of Each Word in a String - February 21, 2024
• C# Program to Find the Longest Word in a String - February 19, 2024