How PIVOT works in SQL? Best PIVOT examples

How PIVOT works in SQL? Best PIVOT examples

When working with SQL, I often find myself dealing with data that could be better represented in a different structure. One of the most powerful tools for reshaping data is the PIVOT operator. It allows me to transform row-based data into a column-based format, making reports and data analysis much more readable. In this article, I’ll break down how PIVOT works in SQL and share the best PIVOT examples for practical use.

What is PIVOT in SQL?

The PIVOT operator in SQL is used to rotate data, converting rows into columns based on a specified aggregate function. It’s particularly useful when I need to summarize data dynamically.

Understanding the Syntax of PIVOT

The basic syntax of the PIVOT operator looks like this:

SELECT <non-pivoted columns>, [first_pivot_column], [second_pivot_column], ...
FROM 
    (SELECT <column_list> FROM <table_name>) AS SourceTable
PIVOT 
    (AGGREGATE_FUNCTION(<pivot_column>) 
    FOR <column_to_pivot> IN ([first_pivot_column], [second_pivot_column], ...)) AS PivotTable;

Let’s break it down:

  • Non-pivoted columns: The columns that remain unchanged.
  • Pivot_column: The data I want to transform into columns.
  • Aggregate function: Usually SUM(), COUNT(), or AVG(), depending on what I need.
  • Column_to_pivot: The values that will become the new column names.

Practical Example of Using PIVOT

To illustrate, let’s consider a sales table:

SalesPerson Year Revenue
John 2023 10000
John 2024 12000
Jane 2023 9000
Jane 2024 11000

If I want to see the total revenue by sales person for each year as columns, I can use PIVOT:

SELECT SalesPerson, [2023] AS "Revenue_2023", [2024] AS "Revenue_2024"
FROM 
    (SELECT SalesPerson, Year, Revenue FROM SalesData) AS SourceTable
PIVOT 
    (SUM(Revenue) FOR Year IN ([2023], [2024])) AS PivotTable;

This would return:

SalesPerson Revenue_2023 Revenue_2024
John 10000 12000
Jane 9000 11000

Key Benefits of Using PIVOT

By using PIVOT, I can achieve:

  • More readable reports
  • Better data aggregation
  • Faster insights from complex datasets

Handling Dynamic PIVOT Scenarios

A common challenge is when I don’t know the column values beforehand. In such cases, I have to generate a dynamic SQL query:

DECLARE @cols NVARCHAR(MAX)
DECLARE @query NVARCHAR(MAX)

SELECT @cols = STRING_AGG(QUOTENAME(Year), ',') 
FROM (SELECT DISTINCT Year FROM SalesData) AS Years

SET @query = 
'SELECT SalesPerson, ' + @cols + ' FROM 
 (SELECT SalesPerson, Year, Revenue FROM SalesData) AS SourceTable
 PIVOT (SUM(Revenue) FOR Year IN (' + @cols + ')) AS PivotTable;'

EXEC sp_executesql @query;

This approach ensures that even if new years appear in the data, they will be included dynamically.

Common Mistakes to Avoid

  1. Forgetting to use an aggregate function – PIVOT requires one.
  2. Not aliasing the PIVOT table – SQL requires an alias for the transformed table.
  3. Hardcoding column names in dynamic datasets – A dynamic PIVOT query is often the best solution.

Conclusion

Mastering how PIVOT works in SQL is essential for any data professional dealing with reports or data transformations. With the best PIVOT examples shown here, I can transform row-based data into a structured report with ease. Whether working with sales data or any kind of grouped information, leveraging PIVOT helps me achieve clearer insights and better presentations.

 

Other interesting article:

How UNNEST works in SQL? Best UNNEST examples