What is the usage of the pivot function in Oracle?

The PIVOT function in Oracle is used to convert row data into column data by transforming multiple values in a single column into multiple columns. Its basic syntax is as follows:

SELECT 列1, 列2, ... 
FROM 表
PIVOT (聚合函数(列名)
       FOR 列名
       IN (值1, 值2, ...))

Column 1, column 2, etc. are non-aggregated columns to be kept. The aggregate function will be applied to the column names (such as SUM, AVG, COUNT, etc.). The column names will be converted to the original column names, and value 1, value 2, etc. are unique values in the original columns.

For example, if we have a table called “sales” with columns such as product, region, quarter, and amount, we can use the PIVOT function to show the total sales amount for each region in each quarter.

SELECT *
FROM (
  SELECT region, quarter, amount
  FROM sales
)
PIVOT (
  SUM(amount)
  FOR region
  IN ('东北' AS NE, '华北' AS NC, '华东' AS EC, '华南' AS SC, '西南' AS SW)
)

The above query will return a result set with quarters as rows and NE (Northeast), NC (North China), EC (East China), SC (South China), SW (Southwest) as columns. Each cell will contain the total sales amount for the corresponding region in that quarter.

It’s important to note that the PIVOT function can only be used in queries and not in DML statements (such as INSERT, UPDATE, DELETE). Additionally, the PIVOT function requires that the original column values must be finite, and cannot be a range or continuous values.