|
/**
* 动态进行行列转换，结果集可在一个可指定的视图中查询（默认为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,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,‘[^,]+‘,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,‘ || 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,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),50)) from dual;
end loop;
end loop;
commit;
end;
/
--查看下数据
select * from salesList a;
--固定行转列
select *
from (select kehu,salesNum from salesList) pivot(
max(salesNum) for shangPin in (
‘上衣‘ as 上衣数量,
‘裤子‘ as 裤子数量,
‘袜子‘ as 袜子数量,
‘帽子‘ as 帽子数量
)
);
--动态行转列
call p_rowtocol(‘select keHu,salesNum from SalesList‘,
‘‘,
‘Sale_RowToCol‘);
select * from Sale_RowToCol;
--完整版
call p_rowtocol(‘select keHu 客户,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行，拼接列别名的时候，添加个双引号把列名包住
?
更新后代码如下：
?
（编辑：威海站长网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
