|
副问题[/!--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的高亮。。仿佛不太友爱
（编辑：湖南网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
