oracle 动态sql列转行_Oracle 行转列 动态出转换的列

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;

完结！！！！！！！࿰