How to Pivot Tables in SQL. A comprehensive guide to creating pivot… | by Jack Chang | Jun, 2024

The crudest way to pivot a table is to utilize the function: DECODE(). DECODE() function is like an if else statement. It compares the input with each value and produces an output.

DECODE(input, value1, return1, value2, return2, …, default)

• input/value: “input” is compared with all the “values”.
• return: if input = value, then “return” is the output.
• default (optional): if input != all of the values, then “default” is the output.

When we know how DECODE() works, it is time to make our first pivot table.

1st Version: Pivot table without total column and row

With DECODE(), we can map out a pseudocode of a pivot table for the ice cream shop owner. When the “day of the week” matches each weekday, DECODE() returns the day’s revenue; if it does not match, 0 is returned instead.

SELECT ice cream flavor, 
SUM(DECODE(day of the week, 'Monday', revenue, 0)) AS MONDAY, SUM(DECODE(day of the week, 'Tuesday', revenue, 0)) AS TUESDAY,
SUM(DECODE(day of the week, 'Wednesday', revenue, 0)) AS WEDNESDAY,
SUM(DECODE(day of the week, 'Thursday', revenue, 0)) AS THURSDAY,
SUM(DECODE(day of the week, 'Friday', revenue, 0)) AS FRIDAY,
SUM(DECODE(day of the week, 'Saturday', revenue, 0)) AS SATURDAY,
SUM(DECODE(day of the week, 'Sunday', revenue, 0)) AS SUNDAY
FROM ice cream shop dataset
WHERE date between last Monday and last Sunday;

2nd Version: Pivot table with total column and row

Great job! Now the ice cream shop owner wants to know more about what happened with last week’s sales. You could upgrade your pivot table by adding a total column and total row.

This could be accomplished using the GROUPING SETS Expression in a GROUP BY statement. A GROUPING SETS Expression defines criteria for multiple GROUP BY aggregations.

GROUPING SETS (attribute1, …, ())

• attribute: a single element or a list of elements to GROUP BY
• (): an empty group, which will become the pivot table’s TOTAL row

SELECT NVL(ice cream flavor, 'TOTAL') "ICE CREAM FLAVOR", 
SUM(DECODE(day of the week, 'Monday', revenue, 0)) AS MONDAY, SUM(DECODE(day of the week, 'Tuesday', revenue, 0)) AS TUESDAY,
SUM(DECODE(day of the week, 'Wednesday', revenue, 0)) AS WEDNESDAY,
SUM(DECODE(day of the week, 'Thursday', revenue, 0)) AS THURSDAY,
SUM(DECODE(day of the week, 'Friday', revenue, 0)) AS FRIDAY,
SUM(DECODE(day of the week, 'Saturday', revenue, 0)) AS SATURDAY,
SUM(DECODE(day of the week, 'Sunday', revenue, 0)) AS SUNDAY, 
SUM(revenue) AS TOTAL
FROM ice cream shop dataset
WHERE date between last Monday and last Sunday
GROUP BY GROUPING SETS (ice cream flavor, ());

Note: NVL() replaces the null row created by () with ‘TOTAL.’ If you are unfamiliar with NVL(), it is simply a function to replace null values.

Another way of calculating the TOTAL column is to add all the revenue from MONDAY to SUNDAY:

SUM(DECODE(day of the week, 'Monday', revenue, 0)) 
+ SUM(DECODE(day of the week, 'Tuesday', revenue, 0)) 
+ SUM(DECODE(day of the week, 'Wednesday', revenue, 0)) 
+ SUM(DECODE(day of the week, 'Thursday', revenue, 0)) 
+ SUM(DECODE(day of the week, 'Friday', revenue, 0)) 
+ SUM(DECODE(day of the week, 'Saturday', revenue, 0))
+ SUM(DECODE(day of the week, 'Sunday', revenue, 0)) AS TOTAL

3rd Version: Pivot table with total column and row and other totals

Say that the ice cream owner wanted one more column on the pivot table you provided: the total number of purchases of each flavor of ice cream. No problem! You can add another “TOTAL” column with the same concept!

SELECT NVL(ice cream flavor, 'TOTAL') "ICE CREAM FLAVOR", 
SUM(DECODE(day of the week, 'Monday', revenue, 0)) AS MONDAY, SUM(DECODE(day of the week, 'Tuesday', revenue, 0)) AS TUESDAY,
SUM(DECODE(day of the week, 'Wednesday', revenue, 0)) AS WEDNESDAY,
SUM(DECODE(day of the week, 'Thursday', revenue, 0)) AS THURSDAY,
SUM(DECODE(day of the week, 'Friday', revenue, 0)) AS FRIDAY,
SUM(DECODE(day of the week, 'Saturday', revenue, 0)) AS SATURDAY,
SUM(DECODE(day of the week, 'Sunday', revenue, 0)) AS SUNDAY, 
SUM(revenue) AS TOTAL,
SUM(purchase ID) "OTHER TOTAL"
FROM ice cream shop dataset
WHERE date between last Monday and last Sunday
GROUP BY GROUPING SETS (ice cream flavor, ());