Simple SQL Tutorial: How to Pivot

Pivoting data is simply an exercise in displaying the data you already have in a different way. It's super useful but can get confusing. My hope is that anyone struggling to write the perfect pivoting query will land on this article and get the answers they need ASAP, so let's dive in!

But Why?

Why are we doing this? The example I've prepared is going to seem a little odd but I am using accessible ideas to illustrate something somewhat complicated. You will encounter problems like this in the real world, so just bear with me.

The Data

Ok, so 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         |
----|------------|----------|-------------|

So you can see, my diet doesn't change much except on the 3rd when I ate 2 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 your cute face I can, with a little magic trick called PIVOT

The Pivot Syntax

The following syntax is going to be in T-SQL, the dialect of SQL used by Microsoft's SQL Server. I'm about 98.7% sure 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

Doesn't make any sense? That's ok, look at a real one:

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-peezy lemon SQ(L)ueezy. 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 |
|-------------|---------|--------|-------|------|

Do you see how this works? It takes the data points you give it and makes columns out of it, and shows the value of another column you specify as the row values.

One note, you 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 terrible, horrible diet.

One thing I don't quite like about this table is all 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's 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 pivoting 101, let me know if you have any questions.

Comments

[JCord]

I use pivots and unpivots a lot.

Thing to note
You showed this but did not mention it, You must use brackets around the value within the in :[value to pivot]

Also is if the results are shown on different rows it becomes of a column not in pivot is different

Unpivot are also great to use

[Mark Saunders]

This is a nice simple tutorial.
What do you do if you don't know the distinct values in the FoodName column?

I need to do a MIN() and MAX() on a field caled YearExp holding an Interger.
I tried a subquery, but it throws syntax errors.

SELECT JobDesc, YearExp
FROM (
SELECT JobDesc, YearExp, Worker FROM Q2
) AS SourceTable
PIVOT (
MIN(YearExp)
FOR YearExp IN (SELECT YearExp FROM Q2)
) AS PivotTable