Oracle数据库如何行转列?如何解决ORA-22922问题
wm_concat()函数
--创建表
create table test(id number,name varchar2(20));
--插入数据
insert into test values(1,'a'); insert into test values(1,'b'); insert into test values(1,'c'); insert into test values(2,'d'); insert into test values(2,'e');
--分组合并
select id,wm_concat(name) name from test group by id;
注:有些数据库版本需要转换成字符串类型,否则报错,如:
select id,to_char(wm_concat(name)) name from test group by id;
pivot()函数
需要Oracle版本大于等于11g
--创建表
create table demo(id int,name varchar(20),nums int);
--插入数据
insert into demo values(1, '苹果', 1000); insert into demo values(2, '苹果', 2000); insert into demo values(3, '苹果', 4000); insert into demo values(4, '橘子', 5000); insert into demo values(5, '橘子', 3000); insert into demo values(6, '葡萄', 3500); insert into demo values(7, '芒果', 4200); insert into demo values(8, '芒果', 5500);
select name, sum(nums) nums from demo group by name;
NAME NUMS
1 葡萄 3500
2 芒果 9700
3 橘子 8000
4 苹果 7000
select * from (select sum(nums) 苹果 from demo where name = '苹果'), (select sum(nums) 橘子 from demo where name = '橘子'), (select sum(nums) 葡萄 from demo where name = '葡萄'), (select sum(nums) 芒果 from demo where name = '芒果');
苹果 橘子 葡萄 芒果
1 7000 8000 3500 9700
select * from (select name, nums from demo) pivot(sum(nums) for name in('苹果' 苹果,'橘子','葡萄','芒果'));
苹果 '橘子' '葡萄' '芒果'
1 7000 8000 3500 9700
注意:
1、pivot(聚合函数 for 列名 in(类型)) ,其中 in('') 中可以指定别名
2、in中还可以指定子查询,比如 select distinct code from customers
3、接上,按照Oracle文档,如果pivot语句中in后面的列不固定,只能使用xml格式的返回结果
问题:
有时候wm_concat()函数转换后,会把数据变为clob格式,此时需要用to_char()函数进行转换,但可能会报“ORA-22922: 不存在的 LOB 值”异常。
解决办法:
1、交换to_char()和wm_concat()函数顺序
to_char(wm_concat(t.site_id)) total_site_id_with_user改为,wm_concat(to_char(t.site_id)) total_site_id_with_user
2、使用listagg()函数替代wm_concat()函数
to_char(listagg(check_day || '(' || model01.count_out_site_id || ')',',') within group (order by model01.count_out_site_id)) count_out_site_id