SQL Pivot and Unpivot Data using T-SQL Code

Problem

In a nightmare, I stare at a blank whiteboard, trying to recall a math formula, but nothing comes. It’s the same feeling the PIVOT operator awakens. For some reason, it doesn’t make sense to me. The syntax feels disjointed, like a recursive CTE. If I sat for an interview today and had to pivot data in T-SQL, I’d pinch myself to wake up. What about you? How well can you pivot data in T-SQL?

Solution

In this tip, I’ll present two ways to pivot data. We’ll explore the reason you pivot data in the first place. How can you pivot when you’re not aggregating on a column? I hope you bookmark this article and use it whenever you need to pivot some data. Who knows, maybe you need it right now. In that case, you’re in luck.

Exploring Pivoting

If you work as a report writer or data analyst, you’ve pivoted data from rows to columns. I bet you pivoted and didn’t know it. A popular method for pivoting data involves creating pivot tables in Excel. Businesspeople love creating pivot tables to analyze data for hidden trends. A joke in the Power BI community is you create the perfect visuals, but the consumer asks, “How can I export this to Excel?” I learned to give in and ensure it’s easy for them to do.

T-SQL offers a built-in operator called PIVOT. Oracle includes the same, but MySQL lacks it. The PIVOT operator converts rows to columns. People pivot on data points like the months of the year or employees. Imagine you create a scheduling report with employees as rows and columns as months. The value contains the total hours a manager assigns each employee. The source table is in a row format. If asked to pivot, it looks like the table below.

SQL offers an UNPIVOT operator that converts columns to rows. I recall using UNPIVOT after someone imported an Excel workbook into SQL as a table. You could also use it if someone created a table and violated every normalization rule.

PIVOT stands out when you know how many columns exist. Sometimes, you don’t know the number upfront. For example, imagine the column list grows as new employees arrive. There’s a way to pivot dynamically; I’ll provide a link at the end.

Building Our Dataset

Let’s create a small dataset to explore PIVOT. The syntax below makes four tables centered around student grades. We’ll add to the dataset, but this gives us a great place to start.

/* MSSQLTips.com */
DROP TABLE IF EXISTS dbo.Students;
CREATE TABLE dbo.Students
(
    Id INT NOT NULL,
    FirstName NVARCHAR(250) NOT NULL,
    LastName NVARCHAR(250) NOT NULL
);
INSERT INTO dbo.Students
(
    Id,
    FirstName,
    LastName
)
VALUES
(1, 'Leonardo', 'Turtle'),
(2, 'Donatello', 'Turtle'),
(3, 'Raphael', 'Turtle'),
(4, 'Michelangelo', 'Turtle');
DROP TABLE IF EXISTS dbo.Class;
CREATE TABLE dbo.Class
(
    Id INT NOT NULL,
    ClassName NVARCHAR(250) NOT NULL
);
INSERT INTO dbo.Class
(
    Id,
    ClassName
)
VALUES
(1, N'Math'),
(2, N'Science'),
(3, N'Pizza Eating');
DROP TABLE IF EXISTS dbo.Grades;
CREATE TABLE dbo.Grades
(
    Id INT NOT NULL,
    ClassId INT NOT NULL,
    StudentId INT NOT NULL,
    Grade INT NOT NULL
);
INSERT INTO dbo.Grades
(
    Id,
    ClassId,
    StudentId,
    Grade
)
VALUES
(1, 1, 1, 92),
(2, 1, 2, 85),
(3, 1, 3, 78),
(4, 1, 4, 55),
(5, 2, 1, 98),
(6, 2, 2, 87),
(7, 2, 3, 77),
(8, 2, 4, 42),
(9, 3, 1, 99),
(10, 3, 2, 85),
(11, 3, 3, 88),
(12, 3, 4, 100);
GO

PIVOT With an Aggregate

PIVOT comes in two varieties: one with an aggregate and one without. We’ll review the former first. Before you add a comment below saying PIVOT always requires a function, keep on reading. Suppose a teacher, we’ll call him Splinter, asks to see student grades with student names as columns. Plus, he needs an average grade. Below is the basic syntax you can use to create a pivot table. In this example, I pivoted on the four student names. As requested, the total represents their average class grade.

/* MSSQLTips.com */
SELECT 'Average Grade' AS Grade,
       [Leonardo],
       [Donatello],
       [Raphael],
       [Michelangelo]
FROM
(
    SELECT s.FirstName,
           g.Grade AS Grade
    FROM dbo.Students s
        LEFT JOIN dbo.Grades g
            ON s.Id = g.StudentId
) AS SourceData
PIVOT
(
    AVG(Grade)
    FOR FirstName IN ([Leonardo], [Donatello], [Raphael], [Michelangelo])
) AS PivotTable;
GO

The original source query is the part after the FROM and before the PIVOT. The bulk of your syntax ends up here. I’ve included the results of the statement below.

Splinter’s happy, but like feeding a stray cat, he’s back for more. He needs to see the individual classes as rows. The syntax below returns the desired results.

/* MSSQLTips.com */
SELECT ClassName,
       [Leonardo],
       [Donatello],
       [Raphael],
       [Michelangelo]
FROM
(
    SELECT s.FirstName,
           c.ClassName,
           g.Grade AS Grade
    FROM dbo.Students s
        LEFT JOIN dbo.Grades g
            ON s.Id = g.StudentId
        LEFT JOIN dbo.Class c
            ON c.Id = g.ClassId
) AS SourceData
PIVOT
(
    AVG(Grade)
    FOR FirstName IN ([Leonardo], [Donatello], [Raphael], [Michelangelo])
) AS PivotTable;
GO

Splinter showers you with praise for your wizard-like T-SQL skills. But he’ll be back; they always come back.

PIVOT Without an Aggregate

When someone asks me for a pivot, it often involves a string column, like an employee or client name. You need to skip an aggregate function in these cases. Microsoft designed PIVOT to require a function. How can you work around this?

Let’s imagine a report that displays approvers on work requests. I’ve included an example below.

You save the records in a row-based fashion at the source. What do you use when there isn’t an aggregate? First, we’ll expand our dataset from above. The code below creates three more tables centered around voting.

/* MSSQLTips.com */
DROP TABLE IF EXISTS dbo.ClassroomImprovements;
CREATE TABLE dbo.ClassroomImprovements
(
    Id INT NOT NULL,
    Name NVARCHAR(250) NOT NULL
);
INSERT INTO dbo.ClassroomImprovements
(
    Id,
    Name
)
VALUES
(1, 'Pizza Quality'),
(2, 'Upgrade Sewer'),
(3, 'New Weapons');
DROP TABLE IF EXISTS dbo.ImprovementVotes;
CREATE TABLE dbo.ImprovementVotes
(
    Id INT NOT NULL,
    ChangeRequestId INT NOT NULL,
    ApproverId INT NOT NULL,
    ApproverType NVARCHAR(250) NOT NULL
);
DROP TABLE IF EXISTS dbo.VoterTypes;
CREATE TABLE dbo.VoterTypes
(
    Id INT NOT NULL,
    Name NVARCHAR(250) NOT NULL
);
INSERT INTO dbo.VoterTypes
(
    Id,
    Name
)
VALUES
(1, 'Voter 1'),
(2, 'Voter 2'),
(3, 'Voter 3');

INSERT INTO dbo.ImprovementVotes
(
    Id,
    ChangeRequestId,
    ApproverId,
    ApproverType
)
VALUES
(1, 1, 4, 1),
(2, 1, 2, 2),
(3, 2, 1, 1),
(4, 3, 1, 1),
(5, 3, 2, 2),
(6, 3, 3, 3);
GO

After finishing the last request, Splinter sends another email requesting a report showing votes for classroom improvements. Only four votes are cast on any improvement. An improvement receives between four and zero votes. Splinter needs to know who voted for what improvement. The code below delivers the expected results.

/* MSSQLTips.com */
;WITH Voters_cte
AS (SELECT ci.Name AS Improvement,
           vt.Name AS VoterType,
           s.FirstName AS StudentName
    FROM dbo.ClassroomImprovements ci
        LEFT JOIN dbo.ImprovementVotes iv
            ON ci.Id = iv.ChangeRequestId
        LEFT JOIN dbo.Students s
            ON s.Id = iv.ApproverId
        LEFT JOIN dbo.VoterTypes vt
            ON vt.Id = iv.ApproverType)
SELECT Improvement,
       [Voter 1],
       [Voter 2],
       [Voter 3],
       [Voter 4]
FROM Voters_cte
    PIVOT
    (
        MAX(StudentName)
        FOR VoterType IN ([Voter 1], [Voter 2], [Voter 3], [Voter 4])
    ) AS p;
GO

I used the MAX function in the example code; MIN also works. Since the data type is a string, SQL returns an error if you swap in an aggregate like SUM or AVG. I often incorporate CTEs in statements like the above. It’s easier to read top-down. If you dislike them, do it another way. The nice thing about SQL is there’s more than one way to solve a problem.

Pivot Without PIVOT

How do you pivot without using the PIVOT operator? I mentioned MySQL lacks the PIVOT operator. Not to worry, there’s another way. Often, developers pick this one when I present both methods. I’ve included the syntax below to give the same results as our last statement.

/* MSSQLTips.com */
;WITH Voters_cte
AS (SELECT ci.Name AS Improvement,
           vt.Name AS VoterType,
           s.FirstName AS StudentName
    FROM dbo.ClassroomImprovements ci
        LEFT JOIN dbo.ImprovementVotes iv
            ON ci.Id = iv.ChangeRequestId
        LEFT JOIN dbo.Students s
            ON s.Id = iv.ApproverId
        LEFT JOIN dbo.VoterTypes vt
            ON vt.Id = iv.ApproverType)
SELECT Improvement,
       MIN(CASE VoterType WHEN 'Voter 1' THEN StudentName END) [Voter 1],
       MIN(CASE VoterType WHEN 'Voter 2' THEN StudentName END) [Voter 2],
       MIN(CASE VoterType WHEN 'Voter 3' THEN StudentName END) [Voter 3],
       MIN(CASE VoterType WHEN 'Voter 4' THEN StudentName END) [Voter 4]
FROM Voters_cte
GROUP BY Improvement;
GO

Which one of these should you use? Microsoft’s website says the PIVOT operator reduces complexity compared to the one above. I beg to differ, but to each their own. Use the one you can remember. I’ve yet to compare the two against a large dataset for performance differences. If someone wants to take on the challenge of comparing them, I want to hear the results.

Key Takeaways

• The PIVOT operator converts rows to columns in SQL Server. UNPIVOT does the opposite.
• There is more than one way to pivot data. You can use the PIVOT operator or the CASE expression from above. Pick whichever one you like. One might perform better when dealing with a large dataset.
• Want to master the PIVOT operator? Write a simple example, then try using the syntax without cheating. If you’re going for an interview, don’t leave your success up to chance. As the golfer Gary Player said, “The more I practice, the luckier I get.”

Next Steps

• Are you interested in learning how to perform a dynamic pivot? Aaron Bertrand wrote Script to create dynamic PIVOT queries in SQL Server. Bookmark it along with this article.
• Save time by creating script templates in Management Studio. Check out the article, Building, and Customizing SQL Server script templates. Building them takes a few minutes, but is worth it in the long run.
• A recursive CTE is another bit of syntax people struggle with. Edwin Sarmiento wrote Recursive Queries using Common Table Expressions (CTE) in SQL Server. Please check it out.
• Here is another related article Understanding PIVOT Syntax in SQL Server