PIVOT 子句

PIVOT 子句

更新时间：2026-10-27 18:07:22

编辑

描述

PIVOT 子句用于将查询结果中的行转换为列，即行转列。它把某一列（或某几列）的取值展开为多个输出列，并对每个输出列分别做聚合，常用于报表类查询。

例如，员工表按部门和职位记录了工资，使用 PIVOT 后可以把各部门的工资汇总值展开为不同的列，一行一个职位，一行内展示该职位在各部门的汇总工资。

说明

PIVOT 子句仅适用于 Oracle 模式。

权限要求

执行 PIVOT 查询时，需要当前用户对参与转换的表或视图拥有 SELECT 权限。有关 OceanBase 数据库权限的详细介绍，参见 Oracle 模式下的权限分类。

语法

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 ] } ... ]
     )

参数解释

参数	说明
`aggregate_function`	指定聚合函数，例如 `SUM`、`COUNT`、`AVG`、`MAX` 等。`PIVOT` 中至少需要指定一个聚合函数。
`expr`	指定聚合函数的参数表达式。
`alias`	可选项，为聚合结果指定别名，会参与生成输出列名。
`pivot_for_clause`	指定要展开为列的字段，即行转列依据的维度列。支持单列和多列两种形式。
`pivot_in_clause`	指定 `pivot_for_clause` 中字段的取值列表，每个取值对应一个输出列。取值只支持常量表达式，不支持子查询。

输出列名生成规则

PIVOT 的输出列由“未参与转换的列”和“每个 IN 取值对应的聚合结果列”两部分组成。

聚合函数指定了别名时，输出列名为 IN 取值的别名与聚合别名的拼接，例如 10 AS dept_10 与 SUM(sal) AS sum_sal 生成 DEPT_10_SUM_SAL。无论指定一个还是多个聚合函数，规则相同。
聚合函数未指定别名时，输出列名为 IN 取值的别名，例如 10 AS dept_10 生成 DEPT_10。
IN 取值未指定别名时，输出列名直接使用该取值，例如 20 生成 20_SUM_SAL；如果聚合函数也没有别名，则生成 20。

说明

了解输出列名生成规则后，可以在外层查询中直接引用这些列，例如 SELECT dept_10_sum_sal FROM ...。

使用限制

pivot_in_clause 中的取值只支持常量表达式，不支持子查询。使用非常量表达式（例如 IN (1 + deptno)）会报错 ORA-56901: non-constant expression is not allowed for pivot|unpivot values。
每个 PIVOT 至少需要指定一个聚合函数，缺少聚合函数属于语法错误，会报错 ORA-00900: You have an error in your SQL syntax。
聚合函数只支持部分类型，包括 SUM、COUNT、AVG、MAX、MIN、STDDEV、VARIANCE、APPROX_COUNT_DISTINCT 等；使用不在该范围内的聚合函数（例如 MEDIAN）会报错 ORA-56902: expect aggregate function inside pivot operation。
暂不支持 PIVOT 作为 joined table 的别名。
暂不支持 PIVOT 操作的目标表是 natural join 的情况。
PIVOT 之后可以继续使用 WHERE、ORDER BY 等子句，此时这些子句作用于转换后的结果集。

示例

创建示例表 emp 并插入数据。

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);

示例 1：按部门对工资做行转列

将各部门（deptno）的工资汇总值展开为列，job 作为行。

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;

查询结果如下：

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

deptno 为 40 的部门没有数据，因此 DEPT_40 列全部为 NULL。

示例 2：同时使用多个聚合函数

在同一行中同时展示每个部门的工资汇总值和人数。

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;

查询结果如下：

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

同一 job 和 deptno 组合没有数据时，聚合函数 SUM 返回 NULL，COUNT 返回 0。

示例 3：`FOR` 子句中使用多列

FOR 子句支持一次指定多个列，IN 子句中使用由相同个数的常量表达式组成的元组。

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
        )
    );

查询结果如下：

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

示例 4：在转换结果上继续过滤

PIVOT 之后可以直接使用 WHERE 子句过滤转换后的结果。

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;

查询结果如下：

+---------+---------+---------+---------+---------+
| JOB     | DEPT_10 | DEPT_20 | DEPT_30 | DEPT_40 |
+---------+---------+---------+---------+---------+
| ANALYST |    NULL |    6000 |    NULL |    NULL |
| CLERK   |    1300 |    1900 |     950 |    NULL |
+---------+---------+---------+---------+---------+
2 rows in set

描述

说明

权限要求

语法

参数解释

输出列名生成规则

说明

使用限制

示例

示例 1：按部门对工资做行转列

示例 2：同时使用多个聚合函数

示例 3：FOR 子句中使用多列

示例 4：在转换结果上继续过滤

相关文档

示例 3：`FOR` 子句中使用多列