|
副标题[/!--empirenews.page--]
?
本文链接：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(‘上衣、裤子、袜子、帽子‘,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,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的高亮。。好像不太友好
（编辑：青岛站长网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
