Oracle 行转列动态出转换的列

淩亂°似流年 2022-10-23 08:29 377阅读 0赞

10月的第二天，前天写了个[Oracle中行转列的pivot的基本使用方法][Oracle_pivot]，然后，因为pivot的用法中，正常情况下，我们需要转出多少个列，都得在我们的sql中完完整整地写出，而不能直接在里面写个查询来动态转换。然后，趁着祖国母亲的生日，这几天放假，整理一下处理方法。

# 一、运行环境 #

Win10，Oracle Database 11g r2，plsql 12。

# 二、效果预览 #

## 1、固定转换列的方法 ##

![db4ad91c3b0a5102c42fdd6c01700289.png][]

## 2、存储过程处理 ##

1）调用存储过程

2）查指定的视图即可

![4dcdc84ec498550d90f43b6ba060d41d.png][]

## 3、两种方法的关系 ##

其实原理很简单，就是通过动态sql，去把你不愿意写，或者说是不确定的转换列数，通过查询查出来，拼接进去，然后执行拼接后的sql，创建视图。

![1bb6a9a401a1f1e2466baf093981af51.png][]

# 三、存储过程 #

create or replace procedure p_RowsToCols(as_sql       in varchar2 --源数据的查询sql
                                            ,as_sql_cols  in varchar2 --动态转换列的查询sql,要求转为列的那列，字段名必须为cols，支持排序
                                            ,as_aggCol    in varchar2 --对应pivot函数的 聚合函数
                                            ,as_changeCol in varchar2 --源数据中，要转为列的字段名
                                            ,as_viewName  in varchar2 --结果输出的视图名，执行完后查此视图即可
                                             ) is
      ls_sql varchar2(4000);
      ls_in  varchar2(4000);
    begin
      --拼接in的内容
      ls_sql := 'select listagg(''''''''||cols||'''''' "''||cols||''"'', '','')within group(order by rn) ' || 
                  'from (select rownum rn, cols from (' || as_sql_cols || '))';
      execute immediate ls_sql
        into ls_in;
     
      --创建视图
      ls_sql := 'create or replace view ' || as_viewName ||' as ' ||
                'select * from (' || as_sql || ') ' ||
                 'pivot (' || as_aggCol || ' for ' || as_changeCol || ' in (' || ls_in || '))';
      execute immediate ls_sql;
    end p_RowsToCols;

# 四、测试数据及SQL #

贴一下我测试的数据和代码。

--建表
    --drop table SalesList;
    create table SalesList(
        keHu                varchar2(20),   --客户
        shangPinId          number(8),      --商品Id
        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 level shangPinId, 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.shangPinId, v_sp.shangPin, floor(dbms_random.value(10,50)) from dual;
         end loop;
      end loop;
      commit;
    end;
    /
     
    --看下源数据
    select * from salesList a;
     
    --固定行转列
    select *
      from (select kehu, shangPin, salesNum from salesList) pivot(
        max(salesNum) for shangPin in (
            '上衣' as 上衣,
            '裤子' as 裤子,
            '袜子' as 袜子,
            '帽子' as 帽子
        )
      );
     
    --动态行转列
    call p_RowsToCols('select keHu, shangPin, salesNum from salesList',
                      'select distinct shangPinId, shangPin cols from salesList order by shangPinId',
                      'max(salesNum)',
                      'shangPin',
                      'sales_RowsToCols');
     
    select * from sales_RowsToCols;

# 完结！！！！！！！！！！！！！！！！！！！！！！ #

结尾来个悲伤的彩蛋，闻者伤心，听者落泪！

上面介绍的方法是一个很简单的思路，然鹅！！！在找到这个思路之前，我还傻傻的做了另外一个版本，一个比较复杂的版本。。。花了有差不多半天时间。我按照这个思路，刚做完的时候有多开心，现在的我，就有多伤心！！不过不忍心直接del掉，还是在这边记录一下吧，思路大概是这个样几：

同样是存储过程，传入数据源的查询sql，通过存储过程处理，拼接成完整的pivot函数需要的sql，然后直接执行，查出结果，insert到一张通用的记录表中，然后建一个视图，指向这部分数据。

# 完结篇.1 效果预览 #

测试数据参考上面的插入sql，同样的数据。

1、第一个参数：必填，数据源的查询，列数不限，但查询的倒数第二列为转换列，最后一列数据列；

2、第二个参数：

1）可为空，内容为要转换出的列的查询sql，可指定表头顺序及显示的表头名称。为空时即从数据源查询的倒数第二列中取distinct值，并不保证排序。

2）查询结果必须为三列，第一列为数字排序列（不需要的话这列就随便指定一个数字就行），第二列对应数据源中转换列的值，第三列即为对应的表头转换后的名称（如果名称不需要改变，这列跟第二列保持一样就行）。

3、第三个参数：可为空，内容就是处理完后要查的视图名。为空的话，即默认为tmp\_rowToCol。

![8cc329606fee75de299a03937e7fc2c8.png][]

![4cb15ca5eb82daaeebb467e2fe244b68.png][]

# 完结篇.2 跟pivot的对应关系 #

![e429eb5b99d87de3c53c20a4ab2678c7.png][]

# 完结篇.3 其他好玩的东西 #

1、因为我们这个方法的话是吧查出的内容存到表里，然后通过视图直接指向数据的，刚才指定的视图Sale\_RowToCol，具体创建语法，就是下面这个，tmp\_RowToCol\_XiaoXianNv为数据存储的表，fbs = '1' 表示是转换的实际数据，fguid是本次转换的一个key。

![70][]

我们查下这个表中实际的数据，这是一个在首次调用存储过程的时候会创建的一个表，203个字段，不超过这么多列的都可以通过我们的存储过程去转并存储数据。

![82ffd229909b976abce25c37bca584ae.png][]

然后，这个表的数据是会一直保存着的，emm...好像没啥卵用，不过说不定哪天脑子抽了向往前查查这个还是挺有意思的。。。（说得我自己都不信）

# 完结篇.4 贴代码 #

csdn的高亮。。好像不太友好

/**
     * 动态进行行列转换，结果集可在一个可指定的视图中查询（默认为tmp_rowToCol）
     * 适用于把一列的值转成多列，转换效果与Oracle的pivot相同，但不需要写死转换出来的每一个列
     * 转换后的数据所存的实体表为tmp_RowToCol_XiaoXianNv，通过一个guid关联到指定的视图
     * tmp_RowToCol_XiaoXianNv表在此过程中不做删除操作。所以如果永久了怕是数据也会挺多。
     * 如果不需要保留数据的话，可以考虑把这个表建为一个会话级临时表，然后转换结果插入后不提交。
     * 这样在同一会话下可查询，提交或者回滚后数据就不复存在。
     * 
     * 转换思路：
     *     1、通过动态sql，拼接出 for XXX in () 里面那部分内容，然后通过动态sql执行并把结果插入一个表中
     *     2、获取固定列、转出列的列名，进行拼接，然后创建视图指向上一步插入的数据
     * 
     * author: lhy
     * date: 2018-10-01 祖国万岁
     * 
     * as_sql             要转换的数据源查询
     *                    对查询结果集的要求：至少3列，
     *                        最后一列为数据值
     *                        倒数第二列为要转成列的内容
     *                        前面的就是不需转换的列
     * as_sql_col         查询要转的列名，如果不指定，即从as_sql的查询的倒数第二列中获取distinct值
     *                    对查询的结果集要求：必须为三列
     *                        对应pivot函数中的：for xxx in('值1' as colNm1，'值2' as colNm2 ...)
     *                        第一列：排序列，要求为数字
     *                        第二列：值（值1..值2）
     *                        第三列：字段名（colNm1..colNm2）
     *                        当然，你不care最后结果的字段的排序和字段名的话，第一列您直接指定一个固定值就行，第三列跟第二列一样也行
     * as_tableName       指定一个视图名来存放转换后的数据,调用存储过程后，通过此视图查询结果集
     */
    create or replace procedure p_rowToCol(as_sql in varchar2, as_sql_col in varchar2, as_viewName in varchar2) is
       lr_curid          integer;            --游标id
       ls_cnt            number(8);          --计数用
       ls_sql            varchar2(4000);     --sql语句
       ls_sql_col        varchar2(4000);     --同 as_sql_col
       ls_rsltTab        dbms_sql.desc_tab;  --存放返回的结果集
       ls_viewName       varchar2(200);      --转换结果存放的表名
       ls_guid           varchar2(50);       --当次转换的guid
       ls_aggColNm       varchar2(50);       --对应pivot的聚合列的列名
       ls_changeColNm    varchar2(50);       --转换列的列名
       ls_cnt_col        number(8);          --要转换出来的列数
       ls_in_text        varchar2(4000);     --对应for()的内容
       ls_cnt_end        number(8);          --最终查询结果的列数
       ls_sql_end        varchar2(4000);     --最终的插入语句
       ls_col_add        varchar2(4000);     --存放转出的列名
       ls_col_fixed      varchar2(4000);     --存放不需要转换的列名
       ls_col_insert     varchar2(4000);     --存放插入的字段
       ls_col_view       varchar2(4000);     --视图的字段
       ls_sql_view       varchar2(4000);     --存放最后的视图的sql
       ls_thead          varchar2(4000);     --拼接一个表头出来，说不定可以回查
     
    begin
      --两步准备工作，其实如果做过一次，后面的代码中其实都不需要执行这两步了
     
       --准备工作1、看下是否存在tmp_RowToCol_XiaoXianNv这个表，首次使用不存在的话建一个（用于存放转换后的数据）
       select count(*) into ls_cnt from all_tables where table_name = upper('tmp_RowToCol_XiaoXianNv');
       if ls_cnt = 0 then
          ls_sql := 'create table tmp_RowToCol_XiaoXianNv(fguid varchar2(50),fopdt date default sysdate,fbs varchar2(8),';
          for i in 1..200 loop
             if i = 200 then
                ls_sql := ls_sql || 'C' || i || ' varchar2(4000))';
             else
                ls_sql := ls_sql || 'C' || i || ' varchar2(4000),';
             end if;
          end loop;
          execute immediate ls_sql;
          --怕以后数据多查询慢的话还可以建个索引给fguid字段
          execute immediate 'create index IDX_ROWSTOCOLS_FGUID on tmp_RowToCol_XiaoXianNv (fguid)';
       end if;
       --准备工作2、看下是否存在一个tmp_XiaoXianNv_t1这个临时表，首次使用不存在的话建一个（用于处理转换列排序）
       select count(*) into ls_cnt from all_tables where table_name = upper('tmp_XiaoXianNv_t1');
       if ls_cnt = 0 then
          ls_sql := 'create global temporary table tmp_XiaoXianNv_t1(fseq NUMBER(20),c1 VARCHAR2(4000),c2 VARCHAR2(4000)) on commit delete rows';
          execute immediate ls_sql;
       end if;
       --取个guid，准备开干
       --这个就是这一次转换的的key，以后要找这次转换的数据都可以拿着这个key到tmp_RowToCol_XiaoXianNv找
       --所以其实也可以通过传参来手动指定这个key，然后以后想查回来这次的数据都会比较方便
       ls_guid := sys_guid();
       --获取转换列和聚合列的列名，即as_sql查询结果的倒数两列
       ls_sql     := as_sql;
       lr_curid   := dbms_sql.open_cursor;
       dbms_sql.parse(lr_curid, ls_sql, dbms_sql.native);
       dbms_sql.describe_columns(lr_curid, ls_cnt, ls_rsltTab);
       ls_changeColNm := ls_rsltTab(ls_cnt - 1).col_name;      --倒数第2列，获取转换列列名
       ls_aggColNm    := ls_rsltTab(ls_cnt).col_name;          --倒数第1列，获取聚合列列名
       ls_cnt         := ls_cnt - 2;                           --不需要转换的列数
       --拼接不需要转换的列名，用于后面建视图（part 1）
       for i in 1..ls_cnt loop
          ls_col_fixed := ls_col_fixed || ls_rsltTab(i).col_name || ', ';
       end loop;
       dbms_sql.close_cursor(lr_curid);
       --拼接 for xxx in ('值1' as colNm1，'值2' as colNm2 ...) 部分
       --获取所有列名并拼接
       --1、先把所有列名的查询sql搞定
       if as_sql_col is null then
          ls_sql_col := 'select rownum rn, c1, c1 c2 from (select distinct '|| ls_changeColNm || ' c1 from (' || as_sql || ') order by ' || ls_changeColNm || ')';
       else
          ls_sql_col := as_sql_col;
       end if;
       --2、把转换列的数据插入到临时表
       execute immediate 'delete from tmp_XiaoXianNv_t1';
       ls_sql        := 'insert into tmp_XiaoXianNv_t1 (fseq, c1, c2) '|| ls_sql_col;
       execute immediate ls_sql;
       --3、ls_cnt_col count出要转换出的列数
       execute immediate 'select count(*) from tmp_XiaoXianNv_t1' into ls_cnt_col;
       --顺便算一下最终查询结果的列数
       ls_cnt_end := ls_cnt + ls_cnt_col;
       --4、拼接for xx in () 里面的内容
       ls_sql := 'select listagg(''''''''||c1||'''''' ''||c2 , '', '') within group(order by fseq ) from tmp_XiaoXianNv_t1 a';
       execute immediate ls_sql into ls_in_text;
       --5、顺便拼接出行转列转换出来的字段名，用于后面建视图（part 2）
       ls_sql := 'select listagg(c2,'','')within group(order by fseq) from tmp_XiaoXianNv_t1';
       execute immediate ls_sql into ls_col_add;
       --拼接插入的表的字段 tmp_RowToCol_XiaoXianNv(c1,c2,c3...)
       select listagg(col, ', ') within group(order by rn)
         into ls_col_insert
         from (select rownum rn, 'c' || rownum col
                 from dual
         connect by rownum <= ls_cnt_end);
       --拼接pivot的insert sql，插入内容，fbs为标识字段，标记为1，即为正式数据
       ls_sql_end      := 'insert into tmp_RowToCol_XiaoXianNv (fguid,fbs,' || ls_col_insert || ') '
                          ||'select '''|| ls_guid ||''' fguid,''1'',t.* from ('
                          || as_sql || ') PIVOT(max(' || ls_aggColNm || ') for ' || ls_changeColNm || ' in ('
                          || ls_in_text || ')) t' ;
       execute immediate ls_sql_end;
       commit;
       
       --拼接表头的字段
       ls_thead        := ls_col_fixed || ls_col_add;
       ls_thead        := replace(ls_thead,' ');
       ls_col_view     := ls_thead;     --转存一下给下面拼接视图的使用
       
       select listagg(''''||col||'''',',')within group(order by rn) 
         into ls_thead from (
              select level rn, regexp_substr(ls_thead,'[^,]+',1,level) col 
                from dual connect by level <= ls_cnt_end
         );
       --拼接pivot的insert sql，插入内容，fbs为标识字段，标记为转换后的字段数，即ls_cnt_end变量，即为正式数据
       ls_sql          := 'insert into tmp_RowToCol_XiaoXianNv (fguid,fbs,' || ls_col_insert || ') values('''|| ls_guid ||''','''||ls_cnt_end||''','||ls_thead||')';
       execute immediate ls_sql;
       commit;
       --拿到结果视图名，默认为tmp_rowToCol
       if as_viewName is null then
          ls_viewName := 'tmp_rowToCol';
       else
          ls_viewName := as_viewName;
       end if;
       /***************************************这部分的代码可以直接删掉************************************
       --上面是根据前面的数据拼接出来的视图的字段ls_col_view，如果我们是只知道一个guid的时候，我们其实也可以去从数据表中查出表头，然后拼接
       ls_sql         := 'select max(fbs) from tmp_RowToCol_XiaoXianNv where fguid = '''||ls_guid||''' and fbs <> ''1''';
       execute immediate ls_sql into ls_cnt_end;  --获取列数
       
       select listagg(col,'||'',''||')within group(order by rn) 
         into ls_sql from (
              select level rn, 'C'||level col from dual connect by level <= ls_cnt_end
         );
       
       ls_sql         := 'select '|| ls_sql || ' from tmp_RowToCol_XiaoXianNv where fguid = '''||ls_guid||''' and fbs <> ''1''';
       execute immediate ls_sql into ls_col_view; --获取视图列名ls_col_view，这里得到的跟上面获取到的是一样的
       **********************************************************************************************/
       --拼接视图的字段别名转换关系  c1 字段1, c2 字段2 ...
       select listagg(c1||' '||col,',')within group(order by rn) 
         into ls_col_view from (
              select 'C'||level c1, level rn, regexp_substr(ls_col_view,'[^,]+',1,level) col 
                from dual connect by level <= ls_cnt_end
         );
       --视图呈现
       ls_sql_view     := 'create or replace view '|| ls_viewName ||' as select '|| ls_col_view || ' from tmp_RowToCol_XiaoXianNv where fbs = ''1'' and fguid = '''|| ls_guid || '''';
       execute immediate ls_sql_view;
    end p_rowToCol;

# 完结篇.5 测试SQL #

--建表
    --drop table SalesList;
    create table SalesList(
        keHu                varchar2(20),   --客户
        shangPinId          number(8),      --商品Id
        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 level shangPinId, 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.shangPinId, v_sp.shangPin, floor(dbms_random.value(10,50)) from dual;
         end loop;
      end loop;
      commit;
    end;
    /
     
    --查看下数据
    select * from salesList a; 
     
    --固定行转列
    select *
      from (select kehu, shangPin, salesNum from salesList) pivot(
        max(salesNum) for shangPin in (
            '上衣' as 上衣数量,
            '裤子' as 裤子数量,
            '袜子' as 袜子数量,
            '帽子' as 帽子数量
        )
      );
     
    --动态行转列
    call p_rowtocol('select keHu, shangPin, salesNum from SalesList',
                    '',
                    'Sale_RowToCol');
     
    select * from Sale_RowToCol; 
     
    --完整版
    call p_rowtocol('select keHu 客户, shangPin, salesNum from SalesList',
                    'select distinct shangPinId, shangPin, shangPin||''数量'' from salesList order by shangPinId',
                    'Sale_RowToCol');
     
    select * from Sale_RowToCol;
     
    --数据存储的表
    select * from tmp_RowToCol_XiaoXianNv

# 结束语？没有 #

啊对，这个存储过程中有建表和建视图的语法，如果你的用户没有权限的话需要用dba用户给一下权限：

grant create table to user;

grant create view to user;

================2019年4月25日 更新================

评论区一个小伙伴报的bug。

拼接出来的sql语句，pivot(xxx for xxx in ('0' 0, '1' 1, '2' 2)) 的这部分，当列为纯数字的时候，别名要加个双引号。

就是说，拼接出来的应该是 pivot(xxx for xxx in ('0' "0", '1' "1", '2' "2")) 才对。

修改内容：代码的11行，拼接列别名的时候，添加个双引号把列名包住

![2019042510434554.png][]

更新后代码如下：

![20190425104453386.png][]

嗯，再次感谢提bug的小伙伴。  
————————————————  
https://blog.csdn.net/Huay\_Li/article/details/82924443

[Oracle_pivot]: https://blog.csdn.net/Huay_Li/article/details/82914161
[db4ad91c3b0a5102c42fdd6c01700289.png]: /images/20221023/3c11f4746cdd4faea47e270082e675b6.png
[4dcdc84ec498550d90f43b6ba060d41d.png]: /images/20221023/cc09b27f18e244fb8bfa201574986a91.png
[1bb6a9a401a1f1e2466baf093981af51.png]: /images/20221023/adbb17cbd8fc4fdeb2aec82b09b5845f.png
[8cc329606fee75de299a03937e7fc2c8.png]: /images/20221023/c62a335593db4dbc889a6c25ad74a232.png
[4cb15ca5eb82daaeebb467e2fe244b68.png]: /images/20221023/b9eb046d22e240a3baafccc5e2036bd7.png
[e429eb5b99d87de3c53c20a4ab2678c7.png]: /images/20221023/54810bc30166489ab54b0018b3f05cad.png
[70]: /images/20221023/5e6d1ed1ae0b4857814006a58459d104.png
[82ffd229909b976abce25c37bca584ae.png]: /images/20221023/f50c243ab46e408c8dbae1b2b6351d87.png
[2019042510434554.png]: /images/20221023/11cbf1ea4f4948ecadc7c272ea2e844f.png
[20190425104453386.png]: /images/20221023/3522762cd0d94c51a6feaf42ed9e068f.png