Pivoting and Un-Pivoting in SQL With Examples

Familiar with Excel's PIVOT function? While SQL doesn't have an exclusive PIVOT function, we can utilize a combination of functions to achieve pivoting in SQL.

Pivoting involves rotating a table by converting unique values from a single column into multiple columns. This rearrangement turns rows into column values, often involving aggregations on remaining columns.

On the other hand, unpivoting reverses this operation by transforming columns into row values.

To demonstrate these two commands, we'll work with the dataset, which you can find in this SQL Tutorial Question.

Table: Sample Data

Pivoting SQL Example: Calculating Engagement Metrics

Let's demonstrate a pivot in SQL by showing the average engagement metrics for each superhero alias across various platforms.

With 4 social media platforms (Instagram, Twitter, TikTok, and YouTube), we'll pivot these platforms from rows into columns using the statement along with the aggregate function.

Let's execute the query below to compute the average engagement metrics:

Check out the results below. Notice how the 4 social media platforms are now pivoted into columns?

The presence of NULL values occurs because certain superheroes lack representation on specific platforms, leading to no recorded engagement rate, thus resulting in NULL values.

Unpivoting SQL Example: Converting Columnnar Engagement Metrics to Individual Platforms

We've learned to pivot from rows to columns, but how about unpivoting from columns to rows?

In this case, we use the statement with multiple conditions to selectively assign the engagement rate corresponding to the respective social media platforms.

Voila! Witness the original table, restored before pivoting.

What's Next: SQL String Functions

While we covered in depth how to deal with numeric data, and process dates/timestamps in SQL, we haven't shown string/text data the same love.

Let's change that in the next tutorial on SQL String Functions!