文档章节

Oracle 行转列 动态出转换的列

o
 osc_bkdv2it5
发布于 2019/08/19 15:00
字数 3574
阅读 23
收藏 0

精选30+云产品,助力企业轻松上云!>>>

 

本文链接: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

o
粉丝 0
博文 500
码字总数 0
作品 0
私信 提问
加载中
请先登录后再评论。
Oracle 行转列pivot 、列转行unpivot 的Sql语句总结

转自:http://blog.csdn.net/xiaokui_wingfly/article/details/42419207 多行转字符串 这个比较简单,用||或concat函数可以实现 实际上就是拆分字符串的问题,可以使用 substr、instr、regex...

Amui
2016/06/29
889
0
SQL行转列:decode函数

  前言   开发中我们经常会用到行转列,这里记录一下我在项目中实现行转列的思路。需求:报表模块,统计某机房机架的不同状态(1 空闲 2 预占 3 占用)的数量(真实需求更为复杂,这里只...

osc_g15m0z3m
2018/11/15
2
0
oracle行转列与列转行

一、行转列   在有些应用场景中,如学生成绩。学生所有科目的成绩都保存到一张表里面,当我们需要以列表的形式显示出学生所对应的每一科目的成绩的时候,需要使用到行转列。   示例 1 --...

烟火_
2015/10/30
0
0
oracle 11g下的行转列pivot填坑--xmlType转clob或String

临近年关,我们给全公司用的API平台也到了要装逼的时刻,然而装逼利器还没搞完,那就是报表呈现,于是leader说你来搞一下吧。 echarts肯定是要的,报表呈现还是很完美的。然后就是数据库层面...

checkboxMan
2018/12/10
178
0
sql server动态行列转换

原文链接:https://www.cnblogs.com/gaizai/p/3753296.html sql server动态行列转换 一.本文所涉及的内容(Contents) 本文所涉及的内容(Contents) 背景(Contexts) 实现代码(SQL Codes...

osc_1k3ioadv
2019/01/08
16
0

没有更多内容

加载失败,请刷新页面

加载更多

Eclipse_JavaEE_Tomcat_MySQL环境配置

安装java环境,配置系统变量(JAVA_HOME,绝对路径) 下载eclipse+Tomcat+mysql window——》preference——》server——》runtime——》tomcat环境 项目右键build path 配mysql jar ,libra...

愿有时光可回首
52分钟前
20
0
MySQL原理 - InnoDB引擎 - 行记录存储 - Redundant行格式

本文基于 MySQL 8 在上一篇:MySQL原理 - InnoDB引擎 - 行记录存储 - Compact格式 中,我们介绍了什么是 InnoDB 行记录存储以及 Compact 行格式,在这一篇中,我们继续介绍其他三种行格式。 ...

zhxhash
今天
29
0
leetcode面试题 17.13(恢复空格)--Java语言实现

求: 哦,不!你不小心把一个长篇文章中的空格、标点都删掉了,并且大写也弄成了小写。像句子"I reset the computer. It still didn’t boot!"已经变成了"iresetthecomputeritstilldidntboo...

拓拔北海
今天
19
0
B站跨年晚会究竟做对了什么?

燃财经(ID:rancaijing)原创 作者 | 赵磊 编辑 | 周昶帆 “补课”是《bilibili晚会 二零一九最美的夜》这个视频中,观众在前两分钟刷得最多的弹幕,寓意着观众是在元旦之后回来补看跨年晚会...

子乾建建_Jeff
01/07
55
0
关于Scrapy爬虫项目运行和调试的小技巧(上篇)

点击上方“Python爬虫与数据挖掘”,进行关注 回复“书籍”即可获赠Python从入门到进阶共10本电子书 今 日 鸡 汤 迟日江山丽,春风花草香。泥融飞燕子,沙暖睡鸳鸯。 扫除运行Scrapy爬虫程序...

yuhan336
04/02
26
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部