Advanced SQL Tutorial Part 5 — SQL PIVOTING 🔨

Photo by Anastasiia Chepinska on Unsplash

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.

Database Support for Pivoting and Unpivoting:

🟠SQL Server:

• Supports both PIVOT and UNPIVOT operators.

🟠Oracle:

• Supports PIVOT and UNPIVOT (from Oracle 11g).

🟠PostgreSQL:

• Does not have built-in PIVOT/UNPIVOT, but can simulate using CASE/FILTER for pivoting and UNION ALL or LATERAL for unpivoting.

🟠MySQL:

• No built-in PIVOT/UNPIVOT. Use CASE and GROUP BY for pivoting, and UNION ALL for unpivoting.

🟠SQLite:

• No built-in PIVOT/UNPIVOT. Similar to MySQL, use CASE and GROUP BY for pivoting, and UNION ALL for unpivoting.

🟠IBM Db2:

• Supports PIVOT and UNPIVOT (as of Db2 11.1).

🟠Snowflake:

• Supports PIVOT and UNPIVOT.

🟠BigQuery:

• Supports PIVOT (using PIVOT in FROM clause) and UNPIVOT (using UNPIVOT).

Pivoting SQL Example: Calculating Engagement Metrics

CASE + MAX() trick to pivot rows into columns.

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 CASE statement along with the MAX() aggregate function.

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

SELECT
  superhero_alias,
  MAX(CASE WHEN platform = 'Instagram' THEN engagement_rate END) AS instagram_engagement_rate,
  MAX(CASE WHEN platform = 'Twitter' THEN engagement_rate END) AS twitter_engagement_rate,
  MAX(CASE WHEN platform = 'TikTok' THEN engagement_rate END) AS tiktok_engagement_rate,
  MAX(CASE WHEN platform = 'YouTube' THEN engagement_rate END) AS youtube_engagement_rate
FROM marvel_avengers
WHERE superhero_alias IN ('Iron Man', 'Captain America', 'Black Widow', 'Thor')
GROUP BY superhero_alias
ORDER BY superhero_alias;

The presence of NULL values occurs because certain superheroes lack representation on specific platforms, resulting in a recorded engagement rate of zero, which in turn leads to NULL values.

Pivoting with PIVOT Function

The general syntax for PIVOT in SQL Server (and similar in Oracle) is:

SELECT ...
FROM ...
PIVOT (
  aggregate_function(column_to_aggregate)
  FOR pivot_column IN ( [value1], [value2], ... [valueN] )
) AS pivot_table_alias

How PIVOT Works

1. Grouping: The PIVOT operation implicitly groups by all columns not mentioned in the aggregation or pivot clause
2. Aggregation: It applies the specified aggregate function to the values
3. Rotation: It rotates the unique values of the pivot column into separate columns

Here’s the query for superheroes rewritten using the PIVOT function, which is supported in SQL Server, Oracle, and other databases that have native PIVOT support:

SELECT 
    superhero_alias,
    COALESCE(Instagram, 0) AS instagram_engagement_rate,
    COALESCE(Twitter, 0) AS twitter_engagement_rate,
    COALESCE(TikTok, 0) AS tiktok_engagement_rate,
    COALESCE(YouTube, 0) AS youtube_engagement_rate
FROM marvel_avengers
PIVOT (
    MAX(engagement_rate)
    FOR platform IN (Instagram, Twitter, TikTok, YouTube)
) AS pivot_table
WHERE superhero_alias IN ('Iron Man', 'Captain America', 'Black Widow', 'Thor')
ORDER BY superhero_alias;

• Groups by superhero_alias (because it's the only column not used in the pivot)
• For each group (each superhero), it will create four columns (Instagram, Twitter, TikTok, YouTube) and compute the MAX(engagement_rate) for each platform.
• Note: If there are multiple rows for the same superhero and platform, the MAX function will choose the highest engagement rate.
• The original query used conditional aggregation (CASE inside MAX) to achieve the same result. The PIVOT syntax is more concise and readable when the pivot columns are known in advance.

Unpivoting SQL Example: Converting Columnar Engagement Metrics to Individual Platforms

unpivoting from columns to rows

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

SELECT
  superhero_alias,
  platform,
  CASE platform
    WHEN 'Instagram' THEN engagement_rate
    WHEN 'Twitter' THEN engagement_rate
    WHEN 'YouTube' THEN engagement_rate
    WHEN 'TikTok' THEN engagement_rate
  END AS engagement_rate
FROM marvel_avengers
WHERE superhero_alias IN ('Iron Man', 'Captain America', 'Black Widow', 'Thor')
ORDER BY superhero_alias;

The original table, restored before pivoting.

UnPivoting with UNPIVOT Function

The UNPIVOT operator is the reverse of the PIVOT operation. It transforms columns into rows, converting a wide table with multiple columns into a longer, narrower table.

Basic UNPIVOT Syntax

SELECT *
FROM source_table
UNPIVOT (
    value_column
    FOR name_column IN (column1, column2, ..., columnN)
) AS unpivot_table_alias

Here’s the query for superheroes rewritten using the UNPIVOT function

SELECT 
    superhero_alias,
    platform,
    engagement_rate
FROM marvel_avengers_wide
UNPIVOT (
    engagement_rate FOR platform IN (
        instagram_engagement_rate AS 'Instagram',
        twitter_engagement_rate   AS 'Twitter',
        youtube_engagement_rate   AS 'YouTube',
        tiktok_engagement_rate    AS 'TikTok'
    )
) AS unpvt
WHERE superhero_alias IN ('Iron Man', 'Captain America', 'Black Widow', 'Thor')
ORDER BY superhero_alias;

In Postgres/MySQL, there’s no native UNPIVOT, you’d simulate it with UNION ALL:

SELECT superhero_alias, 'Instagram' AS platform, instagram_engagement_rate AS engagement_rate
FROM marvel_avengers_wide
UNION ALL
SELECT superhero_alias, 'Twitter', twitter_engagement_rate
FROM marvel_avengers_wide
UNION ALL
SELECT superhero_alias, 'YouTube', youtube_engagement_rate
FROM marvel_avengers_wide
UNION ALL
SELECT superhero_alias, 'TikTok', tiktok_engagement_rate
FROM marvel_avengers_wide
WHERE superhero_alias IN ('Iron Man', 'Captain America', 'Black Widow', 'Thor')
ORDER BY superhero_alias;

Thank you for reading! If you enjoyed this content, feel free to follow for more and show your support by giving it 50 claps! Happy Learning 😊