Purpose
The PIVOT clause is used to transform rows into columns in the query results, that is, to pivot data. It expands the values of one or more columns into multiple output columns and performs aggregation for each output column. It is commonly used in report queries.
For example, an employee table records salaries by department and position. After using PIVOT, you can expand the total salary for each department into different columns, with one row for each position, showing the aggregated salary for that position across all departments within the row.
Note
The PIVOT clause is only applicable in Oracle-compatible mode.
Privilege requirements
When executing a PIVOT query, the current user must have the SELECT privilege on the tables or views involved in the transformation. For more information about OceanBase Database privileges, see Privilege types in Oracle-compatible mode.
Syntax
pivot_clause:
PIVOT
(aggregate_function ( expr ) [[AS] alias ]
[, aggregate_function ( expr ) [[AS] alias ]... ]
pivot_for_clause
pivot_in_clause
)
pivot_for_clause:
FOR { column | ( column [, column... ]) }
pivot_in_clause:
IN
( { { expr | ( expr [, expr... ]) } [ [ AS ] alias ] }
[, { { expr | ( expr [, expr... ]) } [ [ AS ] alias ] } ... ]
)
Parameters
Parameter |
Description |
|---|---|
aggregate_function |
Specify the aggregate function, for example,SUM、COUNT、AVG、MAXetc.PIVOTAt least one aggregate function must be specified in the . |
expr |
Specifies the parameter expression for the aggregate function. |
alias |
Optional. Specifies an alias for the aggregated result, which will be used to generate the output column name. |
pivot_for_clause |
Specifies the field to be expanded into columns, which is the dimension column used for row-to-column conversion. Supports single and multiple columns. |
pivot_in_clause |
Specifypivot_for_clauseThe list of values for the field, where each value corresponds to an output column. Only constant expressions are supported; subqueries are not supported. |
Output column name generation rules
The output columns of PIVOT consist of two parts: "columns not involved in the transformation" and "aggregated result columns corresponding to each IN value".
- When an alias is specified for an aggregate function, the output column name is the concatenation of the alias for the
INvalue and the aggregate alias. For example,10 AS dept_10andSUM(sal) AS sum_salgenerateDEPT_10_SUM_SAL. The rule applies whether one or multiple aggregate functions are specified. - If no alias is specified for an aggregate function, the output column name is the alias of the value in
IN. For example,10 AS dept_10generatesDEPT_10. - If no alias is specified for an
INvalue, the output column name uses that value directly. For example,20generates20_SUM_SAL; if the aggregate function also has no alias, it generates20.
Note
After understanding the output column name generation rules, you can directly reference these columns in the outer query, for example, SELECT dept_10_sum_sal FROM ....
Limitations
- The values in the
pivot_in_clausemust be constant expressions; subqueries are not supported. Using a non-constant expression (for example,IN (1 + deptno)) will result in the errorORA-56901: non-constant expression is not allowed for pivot|unpivot values. - Each
PIVOTclause must specify at least one aggregate function. Omitting an aggregate function is a syntax error that will result in theORA-00900: You have an error in your SQL syntaxerror. - Aggregate functions are supported only for certain types, including
SUM,COUNT,AVG,MAX,MIN,STDDEV,VARIANCE, andAPPROX_COUNT_DISTINCT. Using an aggregate function that is not within this range (for example,MEDIAN) will result in the errorORA-56902: expect aggregate function inside pivot operation. - Currently,
PIVOTis not supported as an alias for a joined table. - The target table for a
PIVOToperation is not currently supported when it involves a natural join. - After
PIVOT, you can continue to use clauses such asWHEREandORDER BY. In this case, these clauses act on the transformed result set.
Examples
Create a sample table named emp and insert data into it.
obclient> CREATE TABLE emp (
empno NUMBER(4),
job VARCHAR2(9),
deptno NUMBER(2),
sal NUMBER(7,2)
);
obclient> INSERT INTO emp VALUES
(7369, 'CLERK', 20, 800),
(7499, 'SALESMAN', 30, 1600),
(7521, 'SALESMAN', 30, 1250),
(7566, 'MANAGER', 20, 2975),
(7654, 'SALESMAN', 30, 1250),
(7698, 'MANAGER', 30, 2850),
(7782, 'MANAGER', 10, 2450),
(7788, 'ANALYST', 20, 3000),
(7839, 'PRESIDENT', 10, 5000),
(7844, 'SALESMAN', 30, 1500),
(7876, 'CLERK', 20, 1100),
(7900, 'CLERK', 30, 950),
(7902, 'ANALYST', 20, 3000),
(7934, 'CLERK', 10, 1300);
Example 1: Pivot wages by department
Spread the total salary for each department (deptno) across columns, with job as rows.
obclient> SELECT *
FROM (SELECT job, deptno, sal FROM emp)
PIVOT (
SUM(sal)
FOR deptno IN (10 AS dept_10, 20 AS dept_20, 30 AS dept_30, 40 AS dept_40)
)
ORDER BY job;
The query result is as follows:
+-----------+---------+---------+---------+---------+
| JOB | DEPT_10 | DEPT_20 | DEPT_30 | DEPT_40 |
+-----------+---------+---------+---------+---------+
| ANALYST | NULL | 6000 | NULL | NULL |
| CLERK | 1300 | 1900 | 950 | NULL |
| MANAGER | 2450 | 2975 | 2850 | NULL |
| PRESIDENT | 5000 | NULL | NULL | NULL |
| SALESMAN | NULL | NULL | 5600 | NULL |
+-----------+---------+---------+---------+---------+
5 rows in set
The department with deptno equal to 40 has no data, so all values in the DEPT_40 column are NULL.
Example 2: Use multiple aggregate functions simultaneously
On the same line, display the total salary and the number of employees for each department.
obclient> SELECT *
FROM (SELECT job, deptno, sal FROM emp)
PIVOT (
SUM(sal) AS sum_sal,
COUNT(sal) AS cnt
FOR deptno IN (10 AS dept_10, 20 AS dept_20, 30 AS dept_30, 40 AS dept_40)
)
ORDER BY job;
The query result is as follows:
+-----------+-----------------+-------------+-----------------+-------------+-----------------+-------------+-----------------+-------------+
| JOB | DEPT_10_SUM_SAL | DEPT_10_CNT | DEPT_20_SUM_SAL | DEPT_20_CNT | DEPT_30_SUM_SAL | DEPT_30_CNT | DEPT_40_SUM_SAL | DEPT_40_CNT |
+-----------+-----------------+-------------+-----------------+-------------+-----------------+-------------+-----------------+-------------+
| ANALYST | NULL | 0 | 6000 | 2 | NULL | 0 | NULL | 0 |
| CLERK | 1300 | 1 | 1900 | 2 | 950 | 1 | NULL | 0 |
| MANAGER | 2450 | 1 | 2975 | 1 | 2850 | 1 | NULL | 0 |
| PRESIDENT | 5000 | 1 | NULL | 0 | NULL | 0 | NULL | 0 |
| SALESMAN | NULL | 0 | NULL | 0 | 5600 | 4 | NULL | 0 |
+-----------+-----------------+-------------+-----------------+-------------+-----------------+-------------+-----------------+-------------+
5 rows in set
When there is no data for the same combination of job and deptno, the aggregate function SUM returns NULL, and COUNT returns 0.
Example 3: Use multiple columns in the FOR clause
The FOR clause supports specifying multiple columns at once, and the IN clause uses tuples composed of constant expressions with the same number of elements.
obclient> SELECT *
FROM (SELECT job, deptno, sal FROM emp)
PIVOT (
SUM(sal) AS sum_sal,
COUNT(1) AS cnt
FOR (deptno, job) IN (
(30, 'MANAGER') AS d30_mgr,
(30, 'CLERK') AS d30_clerk,
(30, 'SALESMAN') AS d30_salesman
)
);
The query result is as follows:
+-----------------+-------------+-------------------+---------------+----------------------+------------------+
| D30_MGR_SUM_SAL | D30_MGR_CNT | D30_CLERK_SUM_SAL | D30_CLERK_CNT | D30_SALESMAN_SUM_SAL | D30_SALESMAN_CNT |
+-----------------+-------------+-------------------+---------------+----------------------+------------------+
| 2850 | 1 | 950 | 1 | 5600 | 4 |
+-----------------+-------------+-------------------+---------------+----------------------+------------------+
1 row in set
Example 4: Filter the converted results further
You can directly use the WHERE clause after PIVOT to filter the transformed results.
obclient> SELECT *
FROM (SELECT job, deptno, sal FROM emp)
PIVOT (
SUM(sal)
FOR deptno IN (10 AS dept_10, 20 AS dept_20, 30 AS dept_30, 40 AS dept_40)
)
WHERE job IN ('ANALYST', 'CLERK')
ORDER BY job;
The query result is as follows:
+---------+---------+---------+---------+---------+
| JOB | DEPT_10 | DEPT_20 | DEPT_30 | DEPT_40 |
+---------+---------+---------+---------+---------+
| ANALYST | NULL | 6000 | NULL | NULL |
| CLERK | 1300 | 1900 | 950 | NULL |
+---------+---------+---------+---------+---------+
2 rows in set