SQL Pivot: A Tutorial

Pivoting data in SQL is an exercise in displaying the data you already have in a different way. It’s useful, but it can get confusing. My hope is that anyone struggling to write the perfect pivoting query in SQL will find the answers they need ASAP, so let’s dive in.

How to Pivot in SQL

SQL pivot allows users to create pivot tables as a way to better interpret and understand the data. To do it, select the data table from SQL, identify the data points you want to view, enter the PIVOT() syntax with an aggregate function like MAX and then enter AS pivot table to display it in that format.

Import the SQL Pivot Data

The example I’ve prepared may seem a little odd, but my goal is to use an accessible idea to illustrate something somewhat complicated. You will encounter problems like this in the real world, so just bear with me.

I have a table called FoodEaten in which I track the foods I eat daily. Here’s what my data looks like:

SELECT * FROM FoodEaten;
-- Results:
Id  | Date       | FoodName | AmountEaten |
--- |------------|----------|-------------|
1   | 2019-08-01 | Sammich  |   2         |
2   | 2019-08-01 | Pickle   |   3         |
3   | 2019-08-01 | Apple    |   1         |
4   | 2019-08-02 | Sammich  |   1         |
5   | 2019-08-02 | Pickle   |   1         |
6   | 2019-08-02 | Apple    |   4         |
7   | 2019-08-03 | Cake     |   2         |
8   | 2019-08-04 | Sammich  |   1         |
9   | 2019-08-04 | Pickle   |   2         |
10  | 2019-08-04 | Apple    |   3         |
----|------------|----------|-------------|

As you can see, my diet doesn’t change much except on the third when I ate two entire cakes. Let’s just call that a cheat day.

What if I wanted to see this presented day by day instead of individual food consumption entries? What if, instead of the way it’s presented now, I’d rather see sandwiches, pickles and apples as columns? Could I do that even though those things are values in a column? You bet I can, with a little magic trick called PIVOT.

More on SQLHow to Optimize SQL for Large Data Sets

SQL Pivot Syntax

The following syntax is going to be in T-SQL, the dialect of SQL used by Microsoft’s SQL Server. I’m confident this will also work in PL/SQL, the Oracle/MySQL flavor of SQL.

The basic idea behind the syntax is this:

SELECT [Data You Break Up The Other Datas With] AS 'a cool name',
[DataPoint1], [DataPoint2], [DataPointEtc]
FROM (
    SELECT [The], [Columns], [With], [Data]
) AS SourceTable
PIVOT (
    AGGREGATE_FUNCTION([Column with data you want displayed in rows])
    FOR [Column in which "The Columns With Data" live] IN (
        [DataPoint1], [DataPoint2], [DataPointEtc]
    )
) AS PivotTable

A tutorial on how to pivot in SQL. | Video: BearededDev

More on SQL10 Advanced SQL Concepts You Should Know for Data Science Interviews

SQL Pivot Example With Code

If that doesn’t make any sense, let’s look at a real example using the data from earlier:

SELECT [Date] AS 'Day',
[Sammich], [Pickle], [Apple], [Cake]
FROM (
    SELECT [Date], FoodName, AmountEaten FROM FoodEaten
) AS SourceTable
PIVOT (
    MAX(AmountEaten)
    FOR FoodName IN ([Sammich], [Pickle], [Apple], [Cake])
) AS PivotTable

Easy. Take a look at the results:

|     DAY     | Sammich | Pickle | Apple | Cake |
|-------------|---------|--------|-------|------|
| 2019-08-01  |    2    |    3   |   1   | NULL |
| 2019-08-02  |    1    |    1   |   4   | NULL |
| 2019-08-03  |  NULL   |  NULL  | NULL  |   2  |
| 2019-08-04  |    1    |    2   |   3   | NULL |
|-------------|---------|--------|-------|------|

It takes the data points you give it and makes columns out of it. It also shows the value of another column you specify as the row values.

You will need an aggregate function on the column after the pivot to ensure one record is returned. If you’re not doing any math, it’s usually pretty safe to use MAX(). You can also use AVG() or SUM() on columns containing numbers for things like daily revenue, monthly average sales, etc. But this time we’re just talking about my diet.

One thing I don’t like about this table is all of the NULLs. If I didn’t eat any cake, I don’t want to see NULL, and if I didn’t eat any apples, I deserve to be shamed with a 0. Here’s the very basic fix for that:

SELECT [Date] AS 'Day',
    ISNULL([Sammich], 0) AS Sammich,
    ISNULL([Pickle],  0) AS Pickle, 
    ISNULL([Apple],   0) AS Apple,
    ISNULL([Cake],    0) AS Cake
FROM (
    SELECT [Date], FoodName, AmountEaten FROM FoodEaten
) AS SourceTable
PIVOT (
    MAX(AmountEaten)
    FOR FoodName IN (
        [Sammich], [Pickle], [Apple], [Cake]
    )
) AS PivotTable

What does that look like, you ask? Behold!

|     DAY     | Sammich | Pickle | Apple | Cake |
|-------------|---------|--------|-------|------|
| 2019-08-01  |    2    |    3   |   1   |   0  |
| 2019-08-02  |    1    |    1   |   4   |   0  |
| 2019-08-03  |    0    |    0   |   0   |   2  |
| 2019-08-04  |    1    |    2   |   3   |   0  |
|-------------|---------|--------|-------|------|

And there you have it. This has been your SQL pivoting 101.

Frequently Asked Questions

What is pivoting in SQL?

Pivoting in SQL is the process of displaying data from a table in a different way. It can divide components of data into different columns and rows to make it easier to analyze.

How do you pivot in SQL?

To pivot in SQL, select the data table from SQL, identify the data points you want to view, enter the PIVOT() syntax with an aggregate function like MAX. Then, enter AS PivotTable to display it in that format.