Master Pivot in Oracle SQL Query with Simple Examples

When it comes to querying databases, Oracle SQL offers a wide array of functions to extract, manipulate, and analyze data. One of these powerful features is the ability to pivot your data, transforming it from rows into columns, or vice versa. This can help you visualize and compare data in a more readable and manageable format. Let's dive into the world of pivoting in Oracle SQL with some practical examples.

Oracle SQL provides the SQL functions PIVOT and UNPIVOT to facilitate these transformations. While they serve the same purpose, their syntax differs slightly. In this article, we will explore both, along with their syntax and usage, providing hands-on examples for each.

Oracle SQL Pivot

The PIVOT function rotates your data from columns into rows, making it easier to calculate and aggregate.

Syntax: `SELECT column, aggregate_function(column_value) FROM table pivoting PIVOT (aggregate_function(column_value) FOR column IN (column1, column2, ...)) OVER (partitions)`

Example: Pivoting years into columns

Consider a table named `sales` with the following data:

Year | Region | Sales
---- | ------ | -----
 2020 | South  |    50
 2020 | North  |   150
 2021 | South  |    80
 2021 | North  |   200

To pivot the 'Year' column into columns, you can use the following query:

SELECT Region, SUM(CASE Year WHEN 2020 THEN Sales ELSE NULL END) AS "2020 Sales",
       SUM(CASE Year WHEN 2021 THEN Sales ELSE NULL END) AS "2021 Sales"
FROM sales
GROUP BY Region;

Example: Pivoting regions into columns

Now, let's pivot the 'Region' column instead. We'll use the PIVOT function this time:

SELECT Year, SUM(Sales) State
FROM sales
PIVOT (SUM(Sales) FOR Region IN ('South', 'North'));

Oracle SQL Unpivot

The UNPIVOT function does the opposite - it takes row data and rotates it into columns.

Syntax: `SELECT column FROM table unpivoting UNPIVOT (column_value FOR column IN (column1, column2, ...)) OVER (partitions)`

Example: Unpivoting columns into rows

Let's start with a different table, `grades`, that stores student grades in multiple subjects as columns:

Student | Math   | Science | History
--------|--------|---------|---------
 Alice  |     90 |       85 |      95
 Bob    |     80 |       75 |      85

To unpivot these columns into rows, you can use the following query:

SELECT Student, Subject, Grade
FROM grades
UNPIVOT (Grade FOR Subject IN ('Math', 'Science', 'History'));