Oracle行转列：Pivot函数和Unpivot函数

介绍

在Oracle数据库中，我们经常需要将一些行数据转换为列数据或将列数据转换为行数据。这种转换对于分析和报表生成非常有用。Oracle提供了两个函数：Pivot函数和Unpivot函数，用于执行这种行转列或列转行的操作。

在本篇博客中，我们将介绍Pivot函数和Unpivot函数的使用方法和示例，并说明它们的主要区别。

Pivot函数

Pivot函数用于将行数据转换为列数据。它将基于一个或多个列的值创建新的列，并将原始数据按照这些列的值进行重组。Pivot函数的基本语法如下：

SELECT [非聚合列], [PIVOT列1], [PIVOT列2], ..., [PIVOT列N]
FROM [表名]
PIVOT ([聚合函数](聚合列)
       FOR [PIVOT列]
       IN ([PIVOT列1], [PIVOT列2], ..., [PIVOT列N]))

在这个语法中，我们首先指定非聚合列（即需要保留为行的列），然后指定要转换为列的列，并为这些列提供一个列列表。聚合函数用于对行进行聚合操作。在IN子句中，我们提供了要转换为列的原始列值的列表。

下面是一个使用Pivot函数的示例：

SELECT *
FROM (SELECT department, salary, job_title
      FROM employee)
PIVOT (SUM(salary)
       FOR job_title
       IN ('Manager', 'Developer', 'Tester'))

在这个示例中，我们将employee表按照job_title列中的值进行分组，并将每个职位的薪水求和。转换后的结果将以department为行和以job_title的值为列的形式返回。

Unpivot函数

Unpivot函数用于将列数据转换为行数据。它将每个指定的列和其对应的值组合成两列的结果集，其中一列是原始列的名称，另一列是该列的值。Unpivot函数的基本语法如下：

SELECT [列1], [列2]
FROM [表名]
UNPIVOT ([列2]
         FOR [列1]
         IN ([要unpivot的列1], [要unpivot的列2], ..., [要unpivot的列N]))

在这个语法中，我们首先指定要返回的两列，然后指定要将其转换为行的列。在IN子句中，我们提供要转换为行的列的列表。

下面是一个使用Unpivot函数的示例：

SELECT *
FROM (SELECT department, 'Manager' AS job_title, manager_salary AS salary, 'Manager' AS salary_type,
             'Developer' AS job_title, developer_salary AS salary, 'Developer' AS salary_type,
             'Tester' AS job_title, tester_salary AS salary, 'Tester' AS salary_type
      FROM department)
UNPIVOT (salary
         FOR (job_title, salary_type)
         IN ((manager_salary, 'Manager'), (developer_salary, 'Developer'), (tester_salary, 'Tester')))

在这个示例中，我们将department表中的薪水数据从列转换为行。转换后的结果将包含job_title、salary和salary_type三个列，其中job_title是薪水所对应的职位，salary是薪水的值，salary_type表示薪水对应的类型（Manager、Developer或Tester）。

主要区别

• Pivot函数用于将行数据转换为列数据，而Unpivot函数用于将列数据转换为行数据。
• Pivot函数需要指定一个聚合函数来对原始行进行聚合操作，而Unpivot函数没有这个要求。
• Pivot函数的列转行的结果可以包含多个列，而Unpivot函数的行转列的结果只会包含两列。

结论

行转列和列转行是常见的数据处理需求，特别是在分析和报表生成方面。Oracle提供了Pivot函数和Unpivot函数，可以帮助我们方便地执行这些操作。通过使用这两个函数，我们可以轻松地将行数据转换为列数据或将列数据转换为行数据，并根据具体需求进行微调和调整。

希望这篇博客能够帮助您理解和使用Oracle中的Pivot函数和Unpivot函数。如果您在使用过程中有任何问题或疑惑，请随时向我们咨询。感谢阅读！

参考资料：

• Oracle官方文档