How to use unpivot in oracle

The UNPIVOT operator converts row data in Oracle into column data for easier analysis and querying. It converts a data set containing multiple rows and columns into a new data set with an ID column representing the original row and a value column containing the values ​​from the original column. The UNPIVOT syntax is: SELECT * FROM UNPIVOT(table_name) AS unpivoted_table[WHERE pivot_condition]. Benefits include simplified queries, improved performance, and support for pivoting. For example, you can convert sales quarter data into columns to easily analyze sales by product and quarter.

UNPIVOT Usage in Oracle

What is UNPIVOT?

UNPIVOT is an Oracle operator used to convert row data to column data. It transforms a multi-row, multi-column data set into a new data set with two columns: an ID column (representing the original rows) and a value column (containing the values ​​from the original columns).

UNPIVOT Usage

UNPIVOT syntax is as follows:

<code>SELECT * FROM UNPIVOT(table_name) [AS unpivoted_table]
[FOR column_name IN (column_list)]
[WHERE pivot_condition]</code>

Among them:

• table_name is The name of the dataset to convert.
• column_name is the name of the original column to be converted to a column.
• column_list is the list of raw columns to be converted to columns.
• pivot_condition is an optional condition used to filter the data to be converted into columns.

Example

Suppose there is a data set named sales that contains the following columns:

• product_id
• product_name
• sales_q1
• sales_q2
• sales_q3
• sales_q4

To convert sales quarter data into columns, you can use the following UNPIVOT query:

<code>SELECT * FROM UNPIVOT(sales) AS unpivoted_sales
FOR sales_quarter IN (sales_q1, sales_q2, sales_q3, sales_q4)</code>

The resulting dataset will look like this:

product_id	product_name	sales_quarter	sales_value
1	Product A	Q1	100
1	Product A	Q2	200
1	Product A	Q3	300
1	Product A	#Q4	400
2	Product B	Q1	500
2	Product B	Q2	600
2	Product B	Q3	700
2	Product B	Q4	800

## Advantages of UNPIVOT

Using UNPIVOT has the following advantages:

Convert row data into column data that is easier to analyze.
• Simplify query and report design.
• Improve the performance of data aggregation and pivot tables.

The above is the detailed content of How to use unpivot in oracle. For more information, please follow other related articles on the PHP Chinese website!