原创

Oracle pivot转置与unpivot反转置

转置将数据行转置为数据的聚集列,反转置从列转置为行,但反转置不是转置的逆运算。转置数据生成聚集,反转置不能撤销转置生成的聚集。
pivot语法:

select ...
from ...
piovt [xml]
(pivot_clause
pivot_for_clause
pivot_in_clause)
where ...

pivot_clause:定义要进行聚集的列。
pivot_for_clause:定义要分组和转置的列。
pivot_in_clause:定义限定结果的值的范围。产生的每个值的聚集转换为单独一列。不支持动态列表,如select deptno from scott.emp

select *
  from (select job, deptno, sal from scott.emp)
pivot(sum(sal) --pivot_clause
   for deptno --pivot_for_clause
in(10, 20, 30, 40)) --pivot_in_clause
 order by job;

可以定义列别名:

select *
  from (select job, deptno, sal from scott.emp)
pivot(sum(sal) sum_sal
   for deptno
in(10 as dept10, 20 as dept20, 30 as dept30, 40 as dept40))
 order by job;

可同时对多个列进行转置。
注意事项:
任何仅在pivot子句中引用的列,不能用在select列表中。
任何仅在pivot for子句中引用的列,不能用在select列表中。
pivot子句中的所有列都必须使用聚集函数。
pivot_in_clause不支持动态列表,如果加上xml关键字,生成的转置数据集就会以xml格式提供结果(可以通过XPath或XQuery表达式处理),此时就可以使用子查询(要保证结果唯一)。

select *
  from (select job, deptno, sal from scott.emp)
pivot xml(sum(sal) sum_sal
   for deptno in (select deptno from scott.emp))
 order by job;

select *
  from (select job, deptno, sal from scott.emp)
pivot xml(sum(sal) sum_sal
   for deptno in (select deptno from scott.emp))
 order by job;

unpivot语法:

select ...
from ...
unpivot [include|exclude nulls]
(unpivot_clause
unpivot_for_clause
unpivot_in_clause)
where ...

unpivot_clause:定义表示反转置值后的列名称。
unpivot_for_clause:定义反转置查询所得到列的列名称。
unpivot_in_clause:定义要进行反转置的已转置列(不是值)的列表。

正文到此结束
该篇文章的评论功能已被站长关闭