Oracle 11g Pivot函数实现列转行

最新推荐文章于 2025-08-28 16:59:33 发布

原创最新推荐文章于 2025-08-28 16:59:33 发布 · 3.7w 阅读

7 ·

CC 4.0 BY-SA版权

SQL & PL/SQL 专栏收录该内容

41 篇文章

订阅专栏

Oracle 11g 的Pivot函数用于将数据表中的行转换为列，通过指定的列进行聚合操作。在示例中，以部门ID为依据展示了如何聚合工资并将其转换为独立的列。此功能在商业智能和数据仓库应用中常见。不仅可按单一列转换，还能根据多个列进行复杂的行转列操作。例如，可以同时计算部门的工资总和和平均值。

先上语法规范：

SELECT ....FROM PIVOT ( aggregate-function() FOR IN (, ,..., ) ) AS WHERE .....

通过一个例子说明其用法：

select * from (select salary, department_id from employee) pivot(sum(salary) as sum_sal for(department_id) in (10,20,30));

10_sum_sal 20_sum_sal 30_sum_sal

370000 155000 370000

在这个例子中，工资按照指定部门做聚集后，成为独立的列显示出来。有几个部门，就有几列；而总是只有一行。即是说，达到了行转列的效果。这种效果的报表在BI/DW应用中很常见。

注意：IN语句是必须的。也就是说，在运行SQL之前必须知道department_id

再进一步，按照多个列作行转列

select * from (select salary, department_id,manager from employee) pivot(sum(salary) for(department_id,manager) in ((10,28),(10,null),(20,null)));

10_28 10_null 20_null

270000 100000 90000

这个SQL查询按照指定部门和经理的组合聚合后的工资。相当于按照两个维度来做聚集。

两个pivot，这时列数增加1倍。但是for() in()内容需要保持一致，否则Oracle无法确定你的列名，列的数据类型。

求各部门总工资和平均工资

select * from (select salary, department_id from employee) pivot(sum(salary) sumsal, avg(salary) avgsal for(department_id) in (10,20,30));

10_sum 10_avg 20_sum 20_avg 30_sum 30_avg

370000 74000 155000 77500 370000 185000