Calculating... until our next FREE Code-Along Session. Secure your spot now

Build Your First Web App Today

Your 14-Day Free Trial Is Waiting To Be Activated
GET INSTANT ACCESS READ MORE ABOUT FIVE

How to Pivot Tables in SQL

Avatar photo
Dominik Keller
Jun 7th, 2024
Blog

Pivoting tables in SQL is a common task for programmers, database administrators (DBAs), data analysts or anyone else querying SQL databases. In SQL, a pivot is used to transform (or pivot) rows of data into columns to generate insights from our data.

Let’s learn how to pivot a table in SQL. We will illustrate how to perform an SQL pivot with an easy-to-understand example.



Why Do We Need to Pivot Tables in SQL?

SQL databases store data in columns and rows. Columns are referred to as fields, and rows as records. Oftentimes, we want to change the way our data is presented to us or our stakeholders. Pivot tables are a tool used for summarizing data and generating insights from it. They are a critical feature in reporting, business intelligence or analysis and let us present data in a more readable, quicker-to-understand format.

Five.Co - A SQL pivot transforms rows into columns

Pivot Tables in SQL

Let’s design a database for global smartphone sales to better understand how pivoting tables can help us extract insights from our dataset.

Our database has five fields (and many more in reality):

  • GUID, which serves as the table’s Primary Key. For the sake of simplicity, let’s use simple numbers as the primary key.
  • Brand
  • Manufacturer
  • Model
  • Month
  • Units Sold

Here is what our database table would look like with five records in it:

GUIDBrandManufacturerModelMonthUnits Sold
1AppleFoxconniPhone XJanuary500
2SamsungSamsung ElectronicsS22January2000
3XiaomiXiaomiMi 11December4500
4AppleFoxconniPhone 14 PlusJuly5000
5SamsungSamsung ElectronicsA55December4500

Even with a small dataset like this, it is difficult to figure out the total monthly number of units sold by model. By pivoting our table, we can create a data view that shows monthly sales by model.

Here’s what we would like our final result to look like. By pivoting our tables, we have moved our month records into the column names, transforming rows into columns. We can now clearly see which model is the best-selling smartphone model by month.

Five.Co - Result of SQL Pivot

Here are the steps we took:

Step 1: We created a simple database table for smartphone sales.


Step 2. We populated the table with the dummy data shown above. Here’s what our table looks like when writing a SELECT * statement.

Five.Co - Select * From produces this output

Step 3. To see each model’s monthly sales we wrote this SQL query, which pivots the table.

SELECT
  `SmartphoneSale`.`Model` As Model,
  SUM(CASE WHEN month = 'January' THEN UnitsSold ELSE 0 END) AS January,
  SUM(CASE WHEN month = 'July' THEN UnitsSold ELSE 0 END) AS July,
  SUM(CASE WHEN month = 'December' THEN UnitsSold ELSE 0 END) AS December
FROM
  `SmartphoneSale`
GROUP BY Model

In this query, we first select each model and then ask SQL to sum up monthly sales by using a CASE statement for each month in our dataset. The corresponding output is exactly what we wanted: a list of smartphones and their monthly sales.


Creating a Web Front End For a SQL Database

We used Five to create the database and write the SQL query above. Five is a rapid application development environment for creating data-driven business software. Each app developed in Five comes with its own MySQL database and an auto-generated admin panel front-end.

Here’s what our SQL pivot query looks like as a data view in our app.

Five.Co - Admin Panel Web App Containing our SQL Pivot as a data view

Five is the fastest way to go from database to web app. Sign up for a free trial to start developing today here.


Go From SQL Database to Web App
Start Developing




Start developing your first application!

Get Started For Free Today

Sign Up Free Book a demo

Build Your Web App With Five

200+ Free Trials Started This Week

Start Free

Thank you for your message!

Our friendly staff will contact you shortly.

CLOSE