Oracle行列转换(pivot和unpivot)
行列转换, 或者说"枢转", 可以使用pivot, unpivot实现
1. pivot 行转列
pivot (行转列)语法:
select columns from table
pivot[XML]( --XML是一个可选关键字, 使用XML格式输出, 大量数据被转化的话需要使用这个
pivot_clause, --查询的数据, 一般是聚合函数.因为需要符合行转列后的查询逻辑
pivot_for_clause, --定义对哪些列进行分组和透视
pivot_in_clause --过滤pivot_for_clause中的值, 每一个值都将作为一个单独的列.
)
准备一些数据, 如下:
一个典型的写法:
select * from students2
pivot(
sum(age) for name in('sdger','erw','wer')
)
结果:
分析:
1. 使用 select * 通常被认为不好, 但是一定要注意此时不能查询一些与逻辑发生冲突的列, 例如: select id 可以, 但是select name, age 却不行, 因为语法本就是行转列 (将name放到行, 数据是age), 尽量由pivot决定显示的内容.
2. 使用sum(age)不能使用age, 就如同使用group by一样, 查询的数据需要满足划分之后的逻辑.
3. 观察上述结果, wer那一列, 它没有发生sum()的计算, 原因很简单wer的id不同, 这说明pivot默认进行全字段分组(这里是对id分了组再计算的sum).
4. null值表示没有数据, 比如没有id=6, 同时name='sdger'的数据, 不能显示其sum(age).
那么, 如果想进行指定分组字段, 例如想不要进行id分组, 同一id的数据合并:
select * from (select name,age from students2)
pivot(
sum(age) for name in('sdger','erw','wer')
)
--使用子查询的方式, 提前约束处理的数据即可.
结果:
使用where:
select * from students2
pivot(
....
)where ....
或者
select .... from (select ... from table where ....)
pivot(
....
)
起别名:
执行多个聚合:
按照多列分组:
使用XML进行pivot:
XML数据展开后如下:
另外:
2. unpivot 列转行
unpivot (列转行)语法:
select * from table
unpivot [include|exclude nulls](
unpivot_clause --指定列名, 指定将来数据所在列的名称
unpivot_for_clause --指定列名, 指定列标题转化成行数据后所在列的名称
unpivot_in_clause --筛选哪些列需要转化
)
一个简单示例:
数据默认不包含NULL值, 即exclude nulls.
如果要包含NUll值:
起别名:
**注意: 别名必须使用单引号否则报错**
