oracle 行转列，动态年份，月份列。已解决！

-----------------存储过程包体-----------

procedure GetComparativeAnalysisTB(p_StartTime                varchar2, ----开始时间
                                 p_EndTime                  varchar2, ----结束时间
                                 p_type                     varchar2, ----1：按全市2：按行业3、按企业
                                 p_id                       varchar2, ----1全部 、区县编码或企业名称
                                 p_id1                      varchar2, ----按企业查询p_id为区县p_id1为企业编码
                                 c_Select                   in out REF_WFData)
   is
cursor cursor_1 IS 
   ---获取 时间段年份
   SELECT (to_number(to_char(TO_DATE(p_StartTime, 'yyyy-MM'), 'YYYY'))  + ROWNUM-1) AS Yearlist
   FROM DUAL
   CONNECT BY ROWNUM <= (to_char(to_date(p_EndTime, 'yyyy-MM'),'yyyy')-to_char(to_date(p_StartTime, 'yyyy-MM'),'yyyy'))+1
   order by Yearlist desc;
 V_SQL VARCHAR2(32767);
/* aYear varchar2(200);---获取年*/
 bMonth varchar2(200);---获取月
 v_StartTime varchar2(200); ---- 开始时间
 v_EndTime varchar2(200);   ----结束时间     
  begin   
    v_StartTime:=p_StartTime||'-01';  
    v_EndTime:=p_EndTime||'-01';  
       if  p_type=1 then    ---统计类别----  按全市
               V_SQL := 'select t1.ps_code,t1.ps_name';
        FOR V_XCLCK IN cursor_1
      LOOP
      SELECT to_char(substr(p_StartTime,6,instr(p_StartTime,'-')-3)) into bMonth FROM DUAL ;--获取月
        V_SQL := V_SQL ||','|| 'min(case when to_char(t1.ps_month,''yyyy-mm'')=''' || V_XCLCK.Yearlist||'-'|| bMonth||''' then t1.pfl end) as ' || 'pfl'||V_XCLCK.Yearlist||bMonth||','
                            || 'min(case when to_char(t1.ps_month,''yyyy-mm'')=''' || V_XCLCK.Yearlist||'-'|| bMonth||''' then t1.fqpfl end) as ' || 'fqpfl'||V_XCLCK.Yearlist||bMonth;
      END LOOP;
       V_SQL := V_SQL || '  from ( 
                                  select 
                                  a.ps_month,
                                  a.c0008_pid ps_code,
                                  a.c0008_item_desc ps_name,
                                  b.ps_pfl fqpfl,
                                  sum(nvl(a.ps_pfl,0)) pfl
                                  from PSINFO_OUTPUTSUBPFYZ_SB a,PSINFO_OUTPUTSUB_SB b
                                  where a.c0003_stcode=b.c0003_stcode
                                  and a.c0070_enterprise_code=b.c0070_enterprise_code
                                  and a.c0007_pcode=b.c0007_pcode 
                                  and a.ps_month=b.ps_month
                                  and a.ps_month>=to_date('''||v_StartTime||''',''yyyy-mm-dd'')
                                  and a.ps_month<=to_date('''||v_EndTime||''',''yyyy-mm-dd'')
                                  and a.c0008_pid in (201,203,207)
                                  and a.ps_state=1 
                                  group by a.c0008_pid,a.c0008_item_desc,a.ps_month,b.ps_pfl
                                  ) t1 
                                  group by ps_code,ps_name
                                  order by ps_code';
      DBMS_OUTPUT.PUT_LINE(V_SQL);
       open c_Select for V_SQL;
         end if;
end GetComparativeAnalysisTB;

 

---

效果图：确定好年份，就会动态循环列头。

————————————————————动态月份列，效果图：显示到具体月份——————————————————————————

cursor cursor_1 IS 
   ---获取 时间段月份
    SELECT TO_CHAR(ADD_MONTHS(TO_DATE(p_StartTime, 'yyyy-MM'), ROWNUM - 1),  'yyyy-MM') as monthlist  
    FROM DUAL  
    CONNECT BY ROWNUM <= months_between(to_date(p_EndTime, 'yyyy-MM'),to_date(p_StartTime, 'yyyy-MM')) + 1 ; 
 V_SQL VARCHAR2(32767);
 aYear varchar2(200);---获取年
 bMonth varchar2(200);---获取月
 v_StartTime varchar2(200); ---- 开始时间
 v_EndTime varchar2(200);   ----结束时间     
  begin   
    v_StartTime:=p_StartTime||'-01';  
    v_EndTime:=p_EndTime||'-01';  
       if  p_type=1 then    ---统计类别----  按全市
            if p_id='0' then
               V_SQL := 'select t1.ps_code,t1.ps_name';
        FOR V_XCLCK IN cursor_1
      LOOP
        SELECT to_char(substr(V_XCLCK.monthlist,1,instr(V_XCLCK.monthlist,'-')-1)),to_char(substr(V_XCLCK.monthlist,6,instr(V_XCLCK.monthlist,'-')-3)) into aYear,bMonth FROM DUAL ;
        V_SQL := V_SQL ||','|| 'nvl(min(case when to_char(t1.ps_month,''yyyy-mm'')=''' || V_XCLCK.monthlist||''' then t1.pfl end),0) as ' || 'pfl'||aYear||bMonth||','
                            || 'nvl(min(case when to_char(t1.ps_month,''yyyy-mm'')=''' || V_XCLCK.monthlist||''' then t1.fqpfl end),0) as ' || 'fqpfl'||aYear||bMonth;
      END LOOP;
       V_SQL := V_SQL || '  from ( 
                                  select 
                                  a.ps_month,
                                  a.c0008_pid ps_code,
                                  a.c0008_item_desc ps_name,
                                  b.ps_pfl fqpfl,
                                  sum(nvl(a.ps_pfl,0)) pfl
                                  from PSINFO_OUTPUTSUBPFYZ_SB a,PSINFO_OUTPUTSUB_SB b
                                  where a.c0003_stcode=b.c0003_stcode
                                  and a.c0070_enterprise_code=b.c0070_enterprise_code
                                  and a.c0007_pcode=b.c0007_pcode 
                                  and a.ps_month=b.ps_month
                                  and a.ps_month>=to_date('''||v_StartTime||''',''yyyy-mm-dd'')
                                  and a.ps_month<=to_date('''||v_EndTime||''',''yyyy-mm-dd'')
                                  and a.c0008_pid in (201,203,207)
                                  and a.ps_state=1 
                                  group by a.c0008_pid,a.c0008_item_desc,a.ps_month,b.ps_pfl
                                  ) t1 
                                  group by ps_code,ps_name
                                  order by ps_code';
      DBMS_OUTPUT.PUT_LINE(V_SQL);
       open c_Select for V_SQL;