Oracle 行转列 pivot函数基本用法

Oracle、sql、plsql 专栏收录该内容
18 篇文章 2 订阅

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 行转列 动态出转换的列

  • 14
    点赞
  • 3
    评论
  • 84
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 技术黑板 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值