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
|Robert Downey Jr.||Tony Stark||Iron Man||500000||200||8.20||12000||800|
|Chris Evans||Steve Rogers||Captain America||300000||150||6.50||8000||500|
|Scarlett Johansson||Natasha Romanoff||Black Widow||700000||300||7.80||15000||1000|
Let's demonstrate a pivot in SQL by showing the average engagement metrics for each superhero alias across various platforms.
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.
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.
Let's change that in the next tutorial on SQL String Functions!
STRING FUNCTIONS 🔤