文章目录
函数wm_concat
1.基本使用
该函数可以把多行记录中的列值以”,”号分隔起来,并显示成一行。
例如有如下表:
执行sql 效果如下:select id,wm_concat(name) from t group by id
2.扩展
利用wm_concat创建视图。
user_tab_columns 表记录了用户表的元数据。
使用如下sql 生成创建视图的语句 :select 'create or replace view v_t as select ' || wm_concat(column_name) ||
' from T' sqlStr
from user_tab_columns
where table_name = 'T';
对于列比较多的表很实用。
函数pivot 和 unpivot
在Oracle 11g中,Oracle 又增加了2个查询:pivot(行转列) 和unpivot(列转行)
行转列 (pivot)
有如下表:
要求显示出每种水果的销量合计,一行显示。select *
from (select name, nums from demo) pivot(sum(nums) for name in('苹果' 苹果,
'橘子',
'葡萄',
'芒果'));
执行上面sql,得到的效果:
对于上面sql 来说 ,pivot 是对 给定结果集 (select name, nums from demo) 按照name进行分组, 貌似是对除了pivot 里边除了 聚合函数列之外的 所有列进行分组。
例如select *
from (select name, nums from demo) pivot( count(1) for name in('苹果',
'橘子',
'葡萄',
'芒果',
'西瓜') ) a ;
则是对name nums 两列进行分组。对于没有分组的列 和 需要旋转的列(in 子句前面的列) 都会在结果中显示 ,如nums.
另外in 子句里可以随意指定,没有则显示空。
但是sql 写成下面会报错,貌似不支持此类型的子查询:select *
from (select name, nums from demo) pivot(sum(nums) for name in(select distinct name from demo));
如果name列比较多的话,in里边自己写那就麻烦了 。这是个问题。以后再找解决方案。
百度了下,xml 类型的时候可以使用any 关键字和子查询,但是返回的结果是xml结构的,如下:select *
from (select name, nums from demo) pivot xml(sum(nums) for name in(select distinct name from demo));
执行结果如下:xml预览图
另外,可以对多列进行旋转。select * from (select salary, department_id from employee) pivot(sum(salary) sumsal, avg(salary) avgsal for(department_id) in (10,20,30))
列转行 unpivot
下面是4中水果4个季度的销售情况:
select id , name, jidu, xiaoshou from Fruit unpivot (xiaoshou for jidu in (q1, q2, q3, q4) )
横向展示出每种水果每个季度的销售情况。
jidu 字段为虚拟列 in 原来的列名,将原来列名 作为当前jidu 的值。将原来列值 作为 xiaoshou虚拟列的 值。