Oracle pivot

### Oracle PIVOT 行转列使用方法示例 Oracle 数据库中的 `PIVOT` 操作用于将行数据转换为列数据，适用于需要对数据进行横向展示的场景。该功能在 Oracle 11g 及以上版本中引入，可以简化传统通过 `DECODE` 或 `CASE WHEN` 实现的复杂 SQL 查询。 #### 基本语法结构 ```sql SELECT * FROM ( -- 子查询，提供基础数据 ) PIVOT ( -- 聚合函数，例如 SUM、MAX 等 FOR 列名 IN (值1 AS 别名1, 值2 AS 别名2, ...) ); ``` #### 示例一：静态列定义 假设有一个表 `SALES_DATA`，记录了不同产品在不同年份的销售金额： | PRODUCT | YEAR | AMOUNT | |---------|------|--------| | A | 2020 | 100 | | A | 2021 | 150 | | B | 2020 | 200 | | B | 2021 | 250 | 要将 `YEAR` 字段的每个值作为列展示，并显示对应的 `AMOUNT`： ```sql SELECT * FROM ( SELECT PRODUCT, YEAR, AMOUNT FROM SALES_DATA ) PIVOT ( SUM(AMOUNT) FOR YEAR IN (2020 AS Y2020, 2021 AS Y2021) ); ``` 执行结果如下： | PRODUCT | Y2020 | Y2021 | |---------|-------|-------| | A | 100 | 150 | | B | 200 | 250 | 此方法适用于列的数量和名称已知的情况[^3]。 #### 示例二：动态列生成（PL/SQL） 当列的数量不确定时，可以通过 PL/SQL 动态构建 SQL 语句。以下是一个简单的动态 `PIVOT` 示例： ```plsql DECLARE v_sql VARCHAR2(4000); v_columns VARCHAR2(4000); BEGIN -- 获取所有可能的列名并拼接成字符串 SELECT LISTAGG('''' || YEAR || ''' AS Y' || YEAR, ', ') WITHIN GROUP (ORDER BY YEAR) INTO v_columns FROM (SELECT DISTINCT YEAR FROM SALES_DATA); -- 构建完整的动态 SQL v_sql := 'SELECT PRODUCT, ' || v_columns || ' FROM ( SELECT PRODUCT, YEAR, AMOUNT FROM SALES_DATA ) PIVOT ( SUM(AMOUNT) FOR YEAR IN (' || v_columns || ') )'; -- 执行动态 SQL EXECUTE IMMEDIATE v_sql; END; ``` 上述代码首先通过 `LISTAGG` 函数动态获取所有 `YEAR` 值并拼接成列名，然后将其插入到 `PIVOT` 查询中，最终通过 `EXECUTE IMMEDIATE` 执行动态生成的 SQL 语句[^2]。 #### 示例三：结合 `DECODE` 进行布尔判断 在某些情况下，除了行转列外，还需要根据列值是否出现返回布尔值。例如，在一个客户类型表中，每条记录代表客户的一个类型，目标是将每个类型横向展开，并标记是否存在： ```sql SELECT CUSTOMER_ID, DECODE(C1, 1, 'true', 'false') AS C1, DECODE(C2, 2, 'true', 'false') AS C2, DECODE(C3, 3, 'true', 'false') AS C3 FROM ( SELECT CUSTOMER_ID, TYPE_CD FROM CUSTOMER_TYPES ) PIVOT ( COUNT(TYPE_CD) FOR TYPE_CD IN (1 AS C1, 2 AS C2, 3 AS C3) ); ``` 此查询中，`COUNT(TYPE_CD)` 用于判断某个类型是否存在，若存在则计数大于 0，否则为 0。再通过 `DECODE` 将其转换为 `'true'` 或 `'false'`，便于前端处理[^3]。 #### 总结 - **静态列**：适用于列数量和名称已知的场景，直接在 `PIVOT` 中指定。 - **动态列**：通过 PL/SQL 动态生成 SQL，适用于列数量未知或经常变化的情况。 - **结合 `DECODE`**：用于将数值型标志转换为布尔值，提升数据可读性。