How to use PIVOT in Oracle? & What is UNPIVOT in Oracle?

PIVOT in Oracle

Data pivoting, which includes moving data from rows to columns, and unpivoting, which involves moving data from columns back to rows, are fundamental concepts in data analysis and reporting. Even though the materials given don’t specifically address the SQL terms PIVOT and UNPIVOT, they do go into length about the fundamental methods needed to do these data transformations using examples like crosstab reporting, advanced grouping, and PL/SQL streaming functions.

Pivoting (Crosstab Reports)

Data is transformed through pivoting, which turns unique values from a particular column (such dates or categories) into unique column headings. Data is categorised and aggregated horizontally in the resulting summary table.

In order to achieve this effect, how to generate a crosstab report utilising the GROUP BY clause, aggregate functions (SUM, COUNT), and conditional logic functions like DECODE or CASE. This is especially helpful for reporting needs that require summaries to be shown across the page instead of down it.

The DECODE function, for instance, is very strong and enables the creation of crosstab tables that display the average, maximum, and lowest checkout times for borrowers in various book categories. Similar to DECODE, the CASE function provides a standard SQL substitute for implementing intricate conditional tests inside queries, which are essential for pivoting data.

Example: Pivoting Data

Using the conditional logic, we first construct a foundational table with product sales by region in order to illustrate the pivoting impact.

Create Table and Insert Values

We build a table called Product_Sales that has a structure akin to many of the transactional tables (such as the BOOKSHELF_CHECKOUT record, which keeps track of things by category and name).

-- DDL Statement: Create the base transactional table
CREATE TABLE Product_Sales (
    Product_Name VARCHAR2(20),
    Region       VARCHAR2(10),
    Sales_Amount NUMBER(8, 2)
);

-- DML Statements: Insert data
INSERT INTO Product_Sales VALUES ('Laptop', 'East', 1000.00);
INSERT INTO Product_Sales VALUES ('Tablet', 'East', 500.00);
INSERT INTO Product_Sales VALUES ('Laptop', 'West', 1200.00);
INSERT INTO Product_Sales VALUES ('Tablet', 'West', 600.00);
COMMIT;

Pivoting Query (Using CASE Expressions for Crosstab)

The unique values in the Region column (‘East’, ‘West’) are pivotted into new columns using CASE expressions (or DECODE), adding up the Sales_Amount for each intersection.

-- SQL Statement: Perform the Pivot operation (Crosstab)
SELECT
    Product_Name,
    SUM(CASE WHEN Region = 'East' THEN Sales_Amount ELSE 0 END) AS East_Sales,
    SUM(CASE WHEN Region = 'West' THEN Sales_Amount ELSE 0 END) AS West_Sales
FROM
    Product_Sales
GROUP BY
    Product_Name;

Output of the Pivoted Data

A traditional crosstab structure is displayed in the resulting pivoted table, which divides the regional data from several rows into two summary columns:

Unpivoting Data

In reverse, unpivoting involves rotating data that is dispersed across multiple columns back into a more row-oriented, normalised structure. In order to do this, two new columns are typically created: one to keep the value held in the original column (the sales amount) and another to hold the original column name (the attribute name, for example, “Region”).

This can be accomplished using basic SQL procedures like set operators (like UNION ALL) or intricate PL/SQL data transformations. The output of several queries must all have the same amount of columns and be compatible with each other’s data types in order for set operators to be used. A table function may be used in advanced transformations to stream data, instantly transforming a single complex row into several output rows. This method is quite effective for enormous data volumes.

In the discussion of table functions, a single input row with multiple measures (like open_price and close_price in a stocktable) can be “pivoted” or transformed into two output rows, each of which contains a measure type (‘O’ or ‘C’) and the associated price. This is a tangible example of complex transformation. Conceptually, unpivoting complex data structures is all about this procedure.

Example: Unpivoting Data

In order to illustrate unpivoting with SQL constructs, we begin with the pivoted data structure and stack the columns vertically using UNION ALL.

Create Table and Insert Values

Assumedly, the Pivoted_Sales table contains the output of the preceding pivot operation.

-- DDL Statement: Create the pivoted table structure
CREATE TABLE Pivoted_Sales (
    Product_Name VARCHAR2(20),
    East_Sales   NUMBER(8, 2),
    West_Sales   NUMBER(8, 2)
);

-- DML Statements: Insert pivoted data
INSERT INTO Pivoted_Sales VALUES ('Laptop', 1000.00, 1200.00);
INSERT INTO Pivoted_Sales VALUES ('Tablet', 500.00, 600.00);
COMMIT;

Unpivoting Query (Using UNION ALL)

We define the new Region column for each set of data by using UNION ALL to pick the East_Sales and West_Sales columns independently and assigning constant string values (‘East’, ‘West’).

-- SQL Statement: Perform the Unpivot operation
SELECT Product_Name, 'East' AS Region, East_Sales AS Sales_Amount FROM Pivoted_Sales
UNION ALL
SELECT Product_Name, 'West' AS Region, West_Sales AS Sales_Amount FROM Pivoted_Sales
ORDER BY Product_Name, Region;

Output of the Unpivoted Data

The unpivoted output restores the original, tall structure of the data:

Advanced Transformation Techniques

Using the UNION ALL strategy for unpivoting or the straightforward CASE/DECODE approach for pivoting may not be effective in situations with extremely high data volumes or sophisticated business logic. PL/SQL table functions and other sophisticated procedural extensions in these situations.

By using a cursor variable (REF CURSOR) as input, which represents the base data, a PL/SQL function can conduct a transformation and return a collection of transformed data. Rows are iteratively returned using the PIPE ROW command as they are formed if the function is specified as PIPELINED, as opposed to waiting for the computation and collection of the complete set. By reducing the need for intermediate data structures and significantly increasing performance, pipelining provides a highly optimised method of executing intricate pivoting or unpivoting logic against the data layer. This sophisticated approach works well for complicated groups, transformations, and report generation that need for more extensive data shaping than what is possible with simple SQL.