Oracle 行转列函数 PIVOT

YNHHJ • 2025-04-24 06:40 • 编程

一、概述

Oracle 行转列函数 PIVOT 是一种非常方便的操作，在处理跨行转换数据的时候非常有效，能够将行数据转为列数据，方便数据的查询和分析。Oracle 行转列函数 PIVOT 主要是通过将数据行转化为数据列，类似于 Excel 的数据透视表功能，通过将聚合函数应用于数据列，实现数据的交叉汇总。

二、PIVOT 的基本语法

SELECT <非聚合列>,       [pivot_clause][
       [subquery]       { FOR <column>          IN <value1>, <value2>, ... <value_n> ]       }      ]
FROM <table>[PIVOT XML]

其中，pivot_clause 包括 PIVOT 操作的聚合列、行列转换所需的列名和列转换对应值的映射。使用 PIVOT XML 可以让结果以 XML 形式返回。

三、PIVOT 的具体应用

a. 按列分组汇总数据

实际运用中有很多这样的场景：需求是按照特定的列进行分组，同时还需要对其他列进行计数、求和这样的聚合计算，所产生的数据结构如下：

| ID | NAME | AGE | GENDER | SCORE || 1  | Tom  | 18  | M      | 90    || 2  | Mary | 19  | F      | 88    || 1  | Tom  | 18  | M      | 95    || 2  | Mary | 19  | F      | 92    || 3  | Jack | 18  | M      | 80    |

可以使用下列 SQL 语句:

SELECT ID, NAME, GENDER,        SUM(SCORE) AS TOTAL_SCORE,        AVG(SCORE) AS AVG_SCORE,        COUNT(*) AS COUNTERFROM SCORES GROUP BY ID, NAME, GENDER;

结果如下表:

| ID | NAME | GENDER | TOTAL_SCORE | AVG_SCORE | COUNTER || 1  | Tom  | M      | 185        | 92.5      | 2       || 2  | Mary | F      | 180        | 90.0      | 2       || 3  | Jack | M      | 80         | 80.0      | 1       |

b. 行列转换

PIVOT 函数可以将行值转换为列值，这个功能在处理某些业务场景时很有用。例如，上面的分组表格，我们可能还需要将结果按照性别进一步划分，得到类似于下面这个表：

| ID | NAME | MALE_SCORE | FEMALE_SCORE || 1  | Tom  | 185        | NULL         || 2  | Mary | NULL         | 180         || 3  | Jack | 80         | NULL         |

可以使用下列 SQL 语句:

SELECT ID, NAME,        MAX(CASE WHEN GENDER = 'M' THEN SCORE ELSE NULL END) AS MALE_SCORE,       MAX(CASE WHEN GENDER = 'F' THEN SCORE ELSE NULL  END) AS FEMALE_SCOREFROM SCORES GROUP BY ID, NAME;

结果如下表:

| ID | NAME | MALE_SCORE | FEMALE_SCORE || 1  | Tom  | 185        | NULL         || 2  | Mary | NULL         | 180         || 3  | Jack | 80         | NULL         |

c. 使用子查询和PIVOT子句完成复杂转换

在实际情况下，PIVOT 有时需要使用子查询来进行更复杂的转换操作，下面来一起看一个 SQL 语句实战：

SELECT *FROM (  SELECT ID, NAME, GENDER, SCORE   FROM SCORES ) PIVOT (  AVG(SCORE) for GENDER IN ('M', 'F') )

这个 SQL 查询操作完成了一个聚合操作，同时将平均值分别按照性别转换为了不同的列，从而完成了数据的透视表聚合。这个应用主要是针对复杂的、跨度广的数据结构，能够方便的将数据转为透视表形式。

四、PIVOT 的注意事项

a. 请避免混用 SUM 和 COUNT 聚合函数

在使用 PIVOT 的时候，如果混用 SUM 和 COUNT 这样的聚合函数，在生成的结果中会出现错误，因此需要避免使用这样的混用写法。

b. 请注意对缺失记录的处理

在使用 PIVOT 函数的时候，我们还需要注意对于缺失的记录需要进行处理，否则会导致 PIVOT 函数中出现空值 null，这样会影响到后续的计算。我们需要在写 PIVOT 函数之前，针对记录的缺失做好充分的预处理工作。

c. 请根据实际情况进行动态拼接

在实际情况中，针对不同的数据表格，需要使用不同的动态拼接操作，才能够最终得到想要的结果，而不是只使用一种固定的规则。因此，我们需要在实际操作中多进行尝试，积累一定的经验。

五、总结

PIVOT 是一种强大的 SQL 函数，它能够将普通的表格数据快速转换为透视表格形式，方便进行数据的查询和汇总，尤其是针对复杂的数据结构，PIVOT 会发挥出非常重要的作用。在实际工作中，针对具体的数据结构，我们需要对 PIVOT 函数进行不断的尝试和探索，多进行数据的拼接和转换，才能够得到更好的结果。

原创文章，作者：YNHHJ，如若转载，请注明出处：https://www.506064.com/n/372250.html