Simple SQL Tutorial: How to Pivot
Tags: sql, tutorial
Published: 2019-08-16 02:57:32

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 :wink:

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`
![image](https://hips.hearstapps.com/hmg-prod.s3.amazonaws.com/images/ross-geller-pivot-1534246982.jpg?crop=1xw:0.9881266490765171xh;center,top&resize=480:*)

## 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:
```sql
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:

```sql
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 `NULL`s. 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:

```sql
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.