本文共 1936 字,大约阅读时间需要 6 分钟。
2018年9月30日22点,眼看着就10月份了,回头看下,8月份就写了一篇博客,9月一篇都没写,想着还是得续一续。
刚好前几天,帮一个群友处理了一个关于Oracle中行转列,根据查询中有的项目,动态转列的做法。想着也挺好玩,不过看下时间,不太充足。所以暂时先写个Oracle自带的行转列函数,pivot的基本用法。国庆几天看下有时间的话完善一下动态转列的做法,到时候再另写一篇附链接过来。
还是先介绍下环境,虽然应该也没啥影响,Win10,Oracle Database 11g r2,plsql 12。
--建表--drop table SalesList;create table SalesList( keHu varchar2(20), --客户 shangPin varchar2(20), --商品名称 salesNum number(8) --销售数量);--插入数据declare --谈几个客户 cursor lr_kh is select regexp_substr('张三、李四、王五、赵六','[^、]+',1, level) keHu from dual connect by level <= 4; --进点货 cursor lr_sp is select regexp_substr('上衣、裤子、袜子、帽子','[^、]+',1, level) shangPin from dual connect by level <= 4;begin --循环插入 for v_kh in lr_kh loop for v_sp in lr_sp loop insert into SalesList select v_kh.keHu, v_sp.shangPin, floor(dbms_random.value(10,50)) from dual; end loop; end loop; commit;end;/
--行转列select * from SalesList pivot( max(salesNum) for shangPin in ( --shangPin 即要转成列的字段 '上衣' as 上衣, --max(salesNum) 此处必须为聚合函数, '裤子' as 裤子, --in () 对要转成列的每一个值指定一个列名 '袜子' as 袜子, '帽子' as 帽子 ) ) where 1 = 1; --这里可以写查询条件,没有可以直接不要where
有时候可能需要行转列的值,即shangPin字段的值的个数很多,或者是不确定个数,那 in () 里面的部分就不好去写死,然后,Oracle的pivot其实也是提供了一个转出动态列的功能,不过转出来的是xml格式的数据。。。这也是为啥我说要自己写一个处理的方法的原因了、、、不过具体做法下次再说,现在先看下原汁原味的转xml的做法,sql如下:
--动态出列(xml的形式)select * from SalesList pivot xml( --pivot xml 以xml的形式输出 max(salesNum) for shangPin in ( select distinct shangPin from SalesList --通过查询查出所有需要转列的值,即所有列名 ) );
还是有点感人的。不过这样的结果,实在是,,用途不大。
所以呢,要么,咱们把这个xml的结果,转换成你要的结果,又要么呢,咱们写个存储过程什么的,通过一些参数,配置,把数据处理成咱们需要的效果。
我个人是比较倾向与存储过程处理,可以通过动态sql拼接,或者是循环的方式处理,具体实现,下期再聊。
===================================一条低调的分割线================================
2018-10-02,动态转换的出来了,有兴趣的可以看下