Oracle pivot函數動态行轉列方法

起因

工作中遇到個問題,需要支援動态行轉列,友善報表展示。

起初這樣寫:👇

select *
 from table_name1 a 
pivot (sum(nvl(citycode,0)) for co in (select distinct citycode from table_name1);

👆👆👆👆👆👆👆👆👆👆👆👆👆👆 ==>絕對報錯.....

           

還有些文章用的隻查詢但是輸出的是XML格式,本人愚鈍,沒看懂。

從網上很多都是介紹如何使用行專列函數 PIVOT的文章,于是在折騰一番無果之後,開始靜下心來思考如何實作。

思考過程

大緻思路如下:

目的: 動态行轉列

困難: pivot 函數不支援 IN( 子查詢)

思路:

1、根據其文法,要實作 IN ( '值’ as 别名) 這樣的想過

2、考慮使用動态SQL拼接

3、嘗試匿名塊試一下

實作過程

1、搭個架構

declare
    v_alias      varchar2(2000);
    v_sql 		 varchar2(4000);

begin
    select ''''||co||''''||' as ' || citycode 
      into v_alias
      from (select 'Total' co , 'Total' citycode,'0000' seq  from dual
            UNION
            select co, citycode,'' from table_name1
            )
   ;
    dbms_output.put_line(v_alias);  --驗證用,可略

    v_sql :='select * from (select co,citycode from table_name1 UNION select ''Total'', ''Total'' from dual) a pivot (sum(nvl(citycode,0)) for co in ('|| v_alias ||'))';	--	真實需求比較複雜,簡化版SQL,拿來驗證是否正确
    dbms_output.put_line(v_sql); --驗證用,可略


exception
    when others then
        dbms_output.put_line('異常'); 
end
;
           

發現多問題:

1、 IN ( v_alias) 效果不理想,無法實作想要的結果

2、N多次的報錯提示

3、思考如何落地,找個載體

2、修修補補

declare
    v_alias              varchar2(2000);
    v_sql_select 		 varchar2(4000);
    v_sql_create         varchar2(4000);
begin
	select listagg(''''||co||''''||' as ' || citycode,',') within group(order by seq)  -- seqs是為了指定total放在第一位,特意加的
      into v_alias
      from (select 'Total' co , 'Total' citycode,'0000' seq  from dual
            UNION
            select co, citycode,'' from table_name1
            )
   ;
    dbms_output.put_line(v_alias);  --驗證用,可略

    v_sql_select :='select * from (select co,citycode from table_name1 UNION select ''Total'', ''Total'' from dual) a pivot (sum(nvl(citycode,0)) for co in ('|| v_alias ||'))';
    dbms_output.put_line(v_sql_select); --驗證用,可略

    EXECUTE IMMEDIATE v_sql_create;
    dbms_output.put_line(sysdate); --驗證用,可略
exception
    when others then
        dbms_output.put_line('異常'); 
end
;
           

1、新增listagg函數,将接出來的結果,有多行合并成一行,并以逗号分隔,就是list資料了

形式為 ‘0000’ as 别名1,‘0001’ as 别名2,…,‘9999’ as 别名N

2、新增view,當作載體,每次執行需要重新建立視圖。真實需求中是每個月執行一次。

結果

經過反複多次就改,最後脫敏後的結果為

declare
    v_alias      varchar2(2000);
    v_sql_select varchar2(4000);
    v_sql_create varchar2(4000);
begin
    select listagg(''''||co||''''||' as ' || citycode,',') within group(order by seq) 
      into v_alias
      from (select 'Total' co , 'Total' citycode,'0000' seq  from dual
            UNION
            select co, citycode,'' from table_name1
            )
   ;
    dbms_output.put_line(v_alias);  --驗證用,可略

    v_sql_select :='select * from (select to_Char(add_months(SYSDATE,-1),''yyyy.mm'') 月份,a.amt,b.co from (select co,amt from summary UNION select ''Total'',nvl(SUM(amt),0) Total from summary) a right join (select co,citycode from table_name1 UNION select ''Total'', ''Total'' from dual) b on a.co=b.co) a pivot (sum(nvl(amt,0)) for co in ('|| v_alias ||'))';
    dbms_output.put_line(v_sql_select); --驗證用,可略

    v_sql_create := 'create or replace view v_banc_summary as '|| v_sql_select || 'with read only';  
    dbms_output.put_line(v_sql_create); --驗證用,可略 

    EXECUTE IMMEDIATE v_sql_create;
    dbms_output.put_line(sysdate); --驗證用,可略


exception
    when others then
        dbms_output.put_line('異常'); 
    
end
;
           

根據實際情況,作成包或者存儲過程都可以。參數與異常處理自行完善,如有需要,拿走不謝。

tips:個人經曆,分享一下,隻為互相學習,共同進步。