oracle wm concat报错,Oracle wm_concat和pivot用法

文章目录

函数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虚拟列的 值。