Oracle 行转列 动态出转换的列
本文链接：https://blog.csdn.net/Huay_Li/article/details/82924443
10月的第二天，前天写了个Oracle中行转列的pivot的基本使用方法，然后，因为pivot的用法中，正常情况下，我们需要转出多少个列，都得在我们的sql中完完整整地写出，而不能直接在里面写个查询来动态转换。然后，趁着祖国母亲的生日，这几天放假，整理一下处理方法。
一、运行环境
Win10，Oracle Database 11g r2，plsql 12。
二、效果预览
1、固定转换列的方法
2、存储过程处理
1）调用存储过程
2）查指定的视图即可
3、两种方法的关系
其实原理很简单，就是通过动态sql，去把你不愿意写，或者说是不确定的转换列数，通过查询查出来，拼接进去，然后执行拼接后的sql，创建视图。
三、存储过程
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。
完结篇.2 跟pivot的对应关系
完结篇.3 其他好玩的东西
1、因为我们这个方法的话是吧查出的内容存到表里，然后通过视图直接指向数据的，刚才指定的视图Sale_RowToCol，具体创建语法，就是下面这个，tmp_RowToCol_XiaoXianNv为数据存储的表，fbs = '1' 表示是转换的实际数据，fguid是本次转换的一个key。
我们查下这个表中实际的数据，这是一个在首次调用存储过程的时候会创建的一个表，203个字段，不超过这么多列的都可以通过我们的存储过程去转并存储数据。
然后，这个表的数据是会一直保存着的，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行，拼接列别名的时候，添加个双引号把列名包住
更新后代码如下：
嗯，再次感谢提bug的小伙伴。
————————————————
版权声明：本文为CSDN博主「huay_li」的原创文章，遵循CC 4.0 by-sa版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/huay_li/article/details/82924443
