Pivoting tables is an essential operation in data analytics, enabling analysts to transform data from a long to a wide format, thus enhancing readability and simplifying reporting. While SQL lacks a built-in PIVOT function akin to those found in some other languages, achieving similar outcomes is possible through the use of CASE statements. This technique is particularly effective when the dimension names are predetermined or there are only a few dimensions to pivot. In this blog post, we explore the static pivot method using CASE statements within SQL, offering a practical example to illustrate the process.

The Scenario

Imagine a dataset that tracks various metrics across different client and department combinations. Our aim is to pivot this data so that each metric becomes a column, making it easier to compare how each client-department pair performs across different metrics.

The Static Pivot Method

The static pivot method involves explicitly specifying the columns you wish to create from the metric names in your dataset. This approach necessitates prior knowledge of the metric names but provides precise control over the output format.

Step 1: Structuring the Query

At the core of our query, we select the client_id and department_id, which will serve as our row identifiers. For each metric that we wish to pivot into a column, we employ a CASE statement within a MAX aggregate function. This setup allows us to convert each metric’s score into a distinct column.

SELECT
  client_id,
  department_id,
  MAX(CASE WHEN metric_name = 'Metric 1' THEN score_percentage END) AS "Metric 1",
  MAX(CASE WHEN metric_name = 'Metric 2' THEN score_percentage END) AS "Metric 2"
  -- Add more CASE statements for additional metrics
FROM
  final_table
GROUP BY
  client_id, department_id;

Step 2: Implementing the CASE Statements

The CASE statements evaluate each row’s metric name. When a match is found, it returns the score_percentage for that metric. The MAX function is then applied to ensure that each client-department pair has only one value per metric, effectively pivoting the data.

Step 3: Grouping the Results

The GROUP BY clause at the query’s end ensures that the results are aggregated by client_id and department_id, yielding a succinct table where each row represents a unique client-department pair, and each column shows a different metric’s score percentage.

Practical Application

Consider an example where we have three metrics: Efficiency, Reliability, and Customer Satisfaction. Our goal is to pivot these metrics for each client-department pair.

SELECT
  client_id,
  department_id,
  MAX(CASE WHEN metric_name = 'Efficiency' THEN score_percentage END) AS "Efficiency",
  MAX(CASE WHEN metric_name = 'Reliability' THEN score_percentage END) AS "Reliability",
  MAX(CASE WHEN metric_name = 'Customer Satisfaction' THEN score_percentage END) AS "Customer Satisfaction"
FROM
  final_table
GROUP BY
  client_id, department_id;

This query will produce a table where each row is a unique combination of client_id and department_id, and each column represents the score percentage for Efficiency, Reliability, and Customer Satisfaction.

Conclusion

The static pivot method using CASE statements offers a flexible and potent means to reshape your data in SQL. Though it requires knowledge of the metrics you wish to pivot upfront, it affords a high degree of control over the format of the output. By adhering to the steps delineated in this guide, you can effectively transform your data for enhanced analysis and decision-making. This technique is particularly valuable in scenarios where dynamic pivoting is unnecessary or when dealing with a fixed set of metrics.