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.
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.
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.
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.