SQL PIVOT

SQL PIVOT is a powerful operation that allows you to transform rows of data into columns, providing a more structured and readable format for reporting and analysis. In SQL Server, you can use the PIVOT operator to achieve this.

The PIVOT operator takes a table-valued expression as input and rotates it by turning the unique values in one column into multiple columns in the output. This transformation typically involves aggregating the values in another column based on the pivoted dimensions.

Syntax

The basic syntax of the PIVOT operator is as follows:

SELECT *
FROM (SELECT column1, column2, ...
      FROM tablename
      ) AS source_table
PIVOT (
      aggregation_function(column3) FOR column4 IN (value1, value2, ...))

Let’s break down this syntax:

SELECT: This part selects all columns from the source table.

FROM (SELECT …): This introduces a subquery that defines the source table for the pivot operation.

AS source_table: This assigns a name to the subquery, making it easier to reference it in the pivot clause.

PIVOT (aggregation_function(column3) FOR column4 IN (value1, value2, …)): This specifies the pivot operation.

aggregation_function(column3): This indicates the aggregation function to be applied to the values of column3. Common aggregation functions include COUNT, SUM, AVG, and MIN.

FOR column4 IN (value1, value2, …): This defines the list of unique values from column4 that will be used to create the new columns in the output.

Examples

Let’s assume we have a table called Sales with the following sample data:

CREATE TABLE Sales (
    ProductCategory VARCHAR(50),
    Year INT,
    Revenue DECIMAL(10, 2)
);

INSERT INTO Sales (ProductCategory, Year, Revenue)
VALUES
    ('Electronics', 2020, 50000.00),
    ('Electronics', 2021, 55000.00),
    ('Electronics', 2022, 60000.00),
    ('Clothing', 2020, 35000.00),
    ('Clothing', 2021, 38000.00),
    ('Clothing', 2022, 40000.00),
    ('Books', 2020, 15000.00),
    ('Books', 2021, 16000.00),
    ('Books', 2022, 17000.00);

PIVOT with aggregation

In this example, we will pivot the data to display the total revenue for each product category for each year.

SELECT *
FROM (
    SELECT ProductCategory, Year, Revenue
    FROM Sales
) AS SourceTable
PIVOT (
    SUM(Revenue)
    FOR Year IN ([2020], [2021], [2022])
) AS PivotTable;

PIVOT with dynamic columns

In some cases, you may not know the exact list of years in advance, and you want to pivot the data dynamically. You can use dynamic SQL to achieve this:

DECLARE @Years NVARCHAR(MAX);
DECLARE @DynamicSQL NVARCHAR(MAX);

SELECT @Years = STRING_AGG(QUOTENAME(Year), ', ') 
FROM (SELECT DISTINCT Year FROM Sales) AS YearList;

SET @DynamicSQL = '
SELECT *
FROM (
    SELECT ProductCategory, Year, Revenue
    FROM Sales
) AS SourceTable
PIVOT (
    SUM(Revenue)
    FOR Year IN (' + @Years + ')
) AS PivotTable';

EXEC sp_executesql @DynamicSQL;

PIVOT with NULL handling

If there are missing values for some combinations of ProductCategory and Year, you can handle them using the ISNULL function to replace NULL values with zeros.

SELECT *
FROM (
    SELECT ProductCategory, Year, ISNULL(Revenue, 0) AS Revenue
    FROM Sales
) AS SourceTable
PIVOT (
    SUM(Revenue)
    FOR Year IN ([2020], [2021], [2022])
) AS PivotTable;

These examples should help you understand how to use the SQL PIVOT operation in SQL Server for various scenarios, including basic pivoting, aggregation, dynamic columns, and handling NULL values. You can adapt these examples to suit your specific data and reporting needs.

Conclusion

SQL PIVOT is a powerful tool that simplifies the process of transforming data from rows to columns and performing aggregations. Its versatility and ease of use make it a valuable asset for data analysts and data scientists who need to manipulate and analyze complex datasets.