架构师

您现在的位置是:首页 > 技术博客 > 数据库

数据库

Oracle数据库如何行转列?如何解决ORA-22922问题

架构师小跟班 2019-08-28 数据库
wm_concat()函数--创建表create table test(id number,name varchar2(20));--插入数据insert into test values(1,'a');insert into test values(1,'b');in


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


文章评论