How to Use Oracle SQL's PIVOT Operator for Data Transformation?

Pivot query in Oracle SQL

Pivot queries allow you to convert data from traditional column format to row format, making it easy to analyze and compare data across different categories.

Create pivot query

To create pivot queries in Oracle SQL, you can use the PIVOT operator. This operator requires you to specify the data you want to pivot, an aggregation function (such as SUM, COUNT, or AVG), and the category values ​​around which you want to pivot.

Example data and queries

Consider the following example data from the tq84_pivot table:

<code>MONTH | VALUE
-------+-----------
1      | 100
2      | 200
3      | 300
4      | 400
5      | 500
6      | 600</code>

To pivot your data around month categories and sum the VALUE column, you can use the following query:

<code class="language-sql">SELECT
  *
FROM
  tq84_pivot
PIVOT (
  SUM(VALUE) AS sum_value
  FOR MONTH IN (1 AS MONTH_JAN, 2 AS MONTH_FEB, 3 AS MONTH_MAR, 4 AS MONTH_APR, 5 AS MONTH_MAI, 6 AS MONTH_JUN)
);</code>

Output results

This query will return the following output:

<code>MONTH_JAN | MONTH_FEB | MONTH_MAR | MONTH_APR | MONTH_MAI | MONTH_JUN
--------+--------+--------+--------+--------+--------
100      | 200      | 300      | 400      | 500      | 600</code>

Oracle 11g and above

Oracle 11g introduces the PIVOT operator, which allows you to create pivot queries in a more concise way. For the same example data, the query can be simplified to:

<code class="language-sql">SELECT
  MONTH_JAN, MONTH_FEB, MONTH_MAR, MONTH_APR, MONTH_MAI, MONTH_JUN
FROM
  tq84_pivot
PIVOT (
  SUM(VALUE)
  FOR MONTH IN (1 AS MONTH_JAN, 2 AS MONTH_FEB, 3 AS MONTH_MAR, 4 AS MONTH_APR, 5 AS MONTH_MAI, 6 AS MONTH_JUN)
);</code>

The above is the detailed content of How to Use Oracle SQL's PIVOT Operator for Data Transformation?. For more information, please follow other related articles on the PHP Chinese website!