PL/SQL UNPIVOT

In Oracle PL/SQL, the UNPIVOT operation is used to transform columns into rows. This is particularly useful when you have data stored in a pivot format, where values from multiple columns need to be turned into rows under a common column. UNPIVOT can be employed to rotate the data in a way that makes it easier to analyze and manipulate.

Syntax

The basic syntax of the UNPIVOT operation in Oracle is as follows:

SELECT *
FROM (
    SELECT column1, column2, column3
    FROM your_table
) 
UNPIVOT (
    unpivoted_value
    FOR unpivoted_column IN (column2, column3)
);

Here’s a breakdown of the components:

SELECT column1, column2, column3 FROM your_table: This is the inner query that selects the columns you want to unpivot.

UNPIVOT (unpivoted_value FOR unpivoted_column IN (column2, column3)): This is the UNPIVOT clause that performs the transformation. It specifies the new column names and the columns to be unpivoted.

Example

Let’s consider an example for better understanding. Suppose you have a table named “sales” with the following structure:

CREATE TABLE sales (
    product_id NUMBER,
    quarter1 NUMBER,
    quarter2 NUMBER,
    quarter3 NUMBER,
    quarter4 NUMBER
);

INSERT INTO sales(product_id, quarter1, quarter2, quarter3, quarter4) 
VALUES (1, 100, 150, 200, 120);
INSERT INTO sales(product_id, quarter1, quarter2, quarter3, quarter4) 
VALUES (2, 80, 90, 110, 95);

If you want to unpivot the quarterly sales data, you can use the UNPIVOT operation as follows:

SELECT *
FROM (
    SELECT product_id, quarter1, quarter2, quarter3, quarter4
    FROM sales
) 
UNPIVOT (
    sales_amount
    FOR quarter IN (quarter1, quarter2, quarter3, quarter4)
);

The result would be:

PRODUCT_ID  SALES_AMOUNT QUARTER
---------- ------------ -------
         1          100 QUARTER1
         1          150 QUARTER2
         1          200 QUARTER3
         1          120 QUARTER4
         2           80 QUARTER1
         2           90 QUARTER2
         2          110 QUARTER3
         2           95 QUARTER4

In this example, the UNPIVOT operation has transformed the quarterly sales data from columns (quarter1, quarter2, quarter3, quarter4) into rows with corresponding values and a new “QUARTER” column indicating the original quarter. This makes it easier to analyze and aggregate the data in various ways.

Conclusion

PL/SQL UNPIVOT is a powerful data transformation operation used in the Oracle Database management system. It is designed to reshape data stored in a table from a wide format (with multiple columns representing different attributes) into a long format (with fewer columns but more rows). This operation is particularly useful when dealing with crosstab or pivot-style data, allowing users to normalize or denormalize their data as needed.

UNPIVOT works by converting columns into rows, creating a more flexible and accessible structure for analysis and reporting. This feature is beneficial for scenarios where data needs to be reorganized for better comprehension or when specific applications or queries require data in a different format.