Oracle: Oracle行转列、列转行的Sql语句总结

例子原型:

select bkg_num,shpr_cde from CD_XLS_UPLOAD_DETAIL where  cd_xls_upload_uuid='' ;

运行结果如下:

Oracle: Oracle行转列、列转行的Sql语句总结

Oracle: Oracle行转列、列转行的Sql语句总结

一、多字段的拼接

将两个或者多个字段拼接成一个字段:

select bkg_num||shpr_cde from CD_XLS_UPLOAD_DETAIL where  cd_xls_upload_uuid='' ;

运行结果:

Oracle: Oracle行转列、列转行的Sql语句总结

二、行转列

将某个字段的多行结果,拼接成一个字段,获取拼接的字符串【默认逗号隔开】

select   wm_concat(bkg_num) from CD_XLS_UPLOAD_DETAIL where  cd_xls_upload_uuid='' ;

运行结果:

,

开拓:如果不想用逗号隔开,可以进行替换:

select replace(wm_concat(bkg_num),',','|') from test;

  也可以进行分组的拼接:

select id,wm_concat(bkg_num) name from test group by id;

三、列转行

原图如下:

Oracle: Oracle行转列、列转行的Sql语句总结

转成行的形式:

Oracle: Oracle行转列、列转行的Sql语句总结

实现的sql:

create table demo(id int,name varchar(20),nums int); ---- 创建表
insert into demo values(1, 'apple', 1000);
insert into demo values(2, 'apple', 2000);
insert into demo values(3, 'apple', 4000);
insert into demo values(4, 'orange', 5000);
insert into demo values(5, 'orange', 3000);
insert into demo values(6, 'grape', 3500);
insert into demo values(7, 'mango', 4200);
insert into demo values(8, 'mango', 5500);
commit;
select name, sum(nums) from demo group by name;
select * from (select name, nums from demo) pivot(sum(nums) for name in ('apple','orange','grape','mango'));  --实现sql

注意: pivot(聚合函数 for 列名 in(类型)) ,其中 in('') 中可以指定别名,in中还可以指定子查询,比如 select distinct code from customers

指定别名如:

select * from (select name, nums from demo) pivot(sum(nums) for name in ('apple' 苹果,'orange' 橘子,'grape' 葡萄,'mango' 芒果));

上一篇:C语言二维数组作为函数参数


下一篇:jz2440开发板设置备份