Convert Rows to columns using 'Pivot' in SQL Server

sql

pivot-engineering

sql-string

dynamic-sql

byNikita Barsukov·Dec 19, 2024

PIVOT is a handy tool in SQL Server for transforming rows into columns. Do it like this:

-- Specify your universe: Products and Years
SELECT Product, [2020], [2021], [2022]
FROM Sales
PIVOT (
    -- Add up all the sales 
    SUM(TotalSales) FOR Year IN ([2020], [2021], [2022])
) AS PivotTable;  -- Fancy a table? Use a table.

In this example, unique Year values are turned into headers, creating a summarization of TotalSales by Product for each year. Adapt column names to suit your data.

The 'surprise me' scenario

In the wild, column names can be as unpredictable as a cat on a hot tin roof. To handle dynamic column names, like week numbers, a nice bit of dynamic SQL comes to the rescue:

-- Grab your future column names
SELECT DISTINCT CONVERT(VARCHAR, WeekNumber) AS WeekNumber
FROM Sales;

Now, build a SQL string and execute it with SQL Server's handy sp_executesql. Let's dive in:

-- Prepare containers for the goodies
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);

-- This isn't just shop talk, we're shopping for unique columns
SELECT @columns = STRING_AGG(QUOTENAME(WeekNumber), ', ') FROM (SELECT DISTINCT WeekNumber FROM Sales) AS Weeks;

SET @sql = '
SELECT *
FROM Sales
PIVOT (
    SUM(TotalSales)
    FOR WeekNumber IN (' + @columns + ') -- Pivot: You spin me right round, baby
) AS PivotTable;';

-- Wham, bam, thank you, ma'am
EXEC sp_executesql @sql;

Handling 'the void'

When pivoting data, empty spaces (known as null in SQL land) may appear. ISNULL swoops in to banish these unwanted guests:

-- Now you see me (null), now you don't
SELECT Product, ISNULL([2020], 0) AS [2020], ISNULL([2021], 0) AS [2021], ISNULL([2022], 0) AS [2022]
FROM Sales
PIVOT (
    SUM(TotalSales) FOR Year IN ([2020], [2021], [2022])
) AS PivotTable;

Optimizing performance and readability

Larger pivot operations might cause snoring. So, enhance performance and readability by employing temporary tables or subqueries:

-- Create a mini-mart for your data
WITH Sales_CTE AS (
    SELECT Product, Year, TotalSales FROM Sales
)
SELECT Product, [2020], [2021], [2022]
FROM Sales_CTE
PIVOT (
    SUM(TotalSales) FOR Year IN ([2020], [2021], [2022])
) AS PivotTable;

The chameleon function

The SUM function isn't the only game in town for pivoting. The MAX function can handle otherwise unmanageable data types, or help you view data through a different lens:

-- Fight for your right for a different view
SELECT Product, MAX([2020]) AS [2020], MAX([2021]) AS [2021], MAX([2022]) AS [2022]
FROM Sales
PIVOT (
    MAX(CustomerComment) FOR Year IN ([2020], [2021], [2022])
) AS PivotTable;

Advanced pivoting

Complex schemas? No problem

Craft stored procedures to handle varying table names and schema definitions:

-- Stored procedures: like an instruction manual, but useful
CREATE PROCEDURE DynamicPivotTable @TableName NVARCHAR(128)
AS
BEGIN
    DECLARE @DynamicPivotQuery NVARCHAR(MAX);
    -- Get creative with your pivot query here based on @TableName
END

Grouping for clarity

Want to group rows before pivoting? Simply invite the GROUP BY clause to the party:

-- All together now!
SELECT Customer, [Monday], [Tuesday], [Wednesday]
FROM Sales
PIVOT (
    SUM(TotalSales) FOR Weekday IN ([Monday], [Tuesday], [Wednesday])
) AS PivotTable
GROUP BY Customer;

Cursor-based dynamic pivoting

Sometimes, a cursor is like a compass, helping you navigate through column names. This handy if they're changing faster than fashion trends:

-- Cursors: The tour guides of columns
DECLARE @ColumnName NVARCHAR(128), @PivotQuery NVARCHAR(MAX);

DECLARE columnCursor CURSOR FOR 
SELECT DISTINCT ColumnName FROM TableName;

OPEN columnCursor;
FETCH NEXT FROM columnCursor INTO @ColumnName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Pivot a column at a time
    FETCH NEXT FROM columnCursor INTO @ColumnName;
END

CLOSE columnCursor;
DEALLOCATE columnCursor;