Simple Pivot Table in SQL + Handling NULL Values in CASE WHEN

Pivot tables are a favorite tool among analysts working in Excel. But what if your data is stored in a SQL database? Can you create something similar in SQL to analyze data in a cross-tab format? The answer is: yes! In this article, I’ll show you how to use GROUP BY and CASE WHEN to build a simple pivot table in SQL.

1. Introduction – What is a Pivot Table in SQL?

Pivot tables allow for data analysis in a format where one axis represents categories and the other axis represents dimensions like years or other metrics. In our case, we want to count the number of orders for different shipping methods (Shipping Mode) by year (Order Year).

2. Extracting Categories – Unique Values in Shipping Mode

The first step is to check the available categories in the Shipping Mode column. We use a simple query:

SELECT DISTINCT shipping_mode 
FROM orders;

The result is a list of shipping methods, such as:

• Second Class
• Standard Class
• First Class
• Same Day

3. Creating a Pivot Table – Using CASE WHEN

Next, we calculate the number of orders for each shipping method, grouped by year. Using GROUP BY and the CASE WHEN construct, we assign each shipping method to a separate column:

SELECT 
    YEAR(order_date) AS order_year,
    COUNT(CASE WHEN shipping_mode = 'Second Class' THEN order_id END) AS second_class,
    COUNT(CASE WHEN shipping_mode = 'Standard Class' THEN order_id END) AS standard_class,
    COUNT(CASE WHEN shipping_mode = 'First Class' THEN order_id END) AS first_class,
    COUNT(CASE WHEN shipping_mode = 'Same Day' THEN order_id END) AS same_day
FROM 
    orders
GROUP BY 
    order_year
ORDER BY 
    order_year;

4. The Result – Analyzing Orders by Year and Shipping Methods

After executing the query, we get a table that resembles a traditional pivot table. Example data might look like this:

Each column shows the number of orders placed in a given year for a specific shipping method. This allows for easy trend analysis.

5. Handling NULL Values in CASE WHEN

It’s worth noting that in SQL, NULL values are not counted in the COUNT function. This means we don’t need to define ELSE NULL in our CASE WHEN statement. As a result, only orders meeting the condition, such as shipping_mode = 'Second Class', are counted.

6. Conclusion

Creating pivot tables in SQL isn’t as difficult as it seems. Using GROUP BY and CASE WHEN, you can easily analyze data in a cross-tab format. This simple example shows how to build a dynamic analysis that provides valuable insights.

If you enjoyed this example, be sure to check out my other materials where I cover more advanced SQL functions and demonstrate effective data analysis techniques in Excel, Python, and Tableau!

Prefer reading in Polish? No problem!

Other interesting articles:

• Data Cleaning Techniques: A Step-by-Step Guide for Data Analysts
• 9 LinkedIn Mistakes That Are Killing Your Job Search
• Variable, data types and operators in Python
• How to Successfully Transition into a Data Analyst Career: Essential Skills and Tips

That’s all on this topic. Analyze in peace!

Did you like this article 🙂?
Share it on Social Media 📱
>>> You can share it on LinkedIn and show that you learn something new every day.
>>> You can throw it on Facebook – and perhaps help a friend of yours who is looking for this.
>>> And remember to bookmark this page, you never know if it won’t come handy in in the future.

You prefer to watch 📺 – no problem
>>> Subscribe and watch my English channel on YouTube.