Oracle 行转列 pivot函数基本用法

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;
/

四、pivot进行转换的SQL（查询结果就是上面的结果图）

--行转列
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，动态转换的出来了，有兴趣的可以看下

Oracle 行转列 动态出转换的列