oracle 运用存储过程解决动态行转列完成实际生产需要

建表语句
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; 得到下列结果
   
    KEHU    SHANGPINID  SHANGPIN    SALESNUM
1   张三  1   上衣  33
2   张三  2   裤子  25
3   张三  3   袜子  12
4   张三  4   帽子  49
5   李四  1   上衣  19
6   李四  2   裤子  29
7   李四  3   袜子  24
8   李四  4   帽子  12
9   王五  1   上衣  38
10  王五  2   裤子  35
11  王五  3   袜子  17
12  王五  4   帽子  13
13  赵六  1   上衣  19
14  赵六  2   裤子  21
15  赵六  3   袜子  20
16  赵六  4   帽子  12

--固定行转列
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');
 
--以上两种查询结果均为:
    KEHU    上衣  裤子  袜子  帽子
1   王五  38  35  17  13
2   赵六  19  21  20  12
3   李四  19  29  24  12
4   张三  33  25  12  49

--动态行转列执行得是以下存储过程：
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;