Oracle在存储过程中使用pivot函数实现列转行

 

这个表是关联查询出来的结果，sql为

select
GG_HRZXX.HRZMC AS hrzmc,
ENERGY_DAYHEATINFO.NIANDU AS niandu,
ENERGY_DAYHEATINFO.RQDATE AS rqdate,
ENERGY_DAYHEATINFO.SJRNH AS sjrnh,
ENERGY_DAYHEATINFO.Sfmj as sfmj
from ENERGY_DAYHEATINFO,GG_HRZXX
where ENERGY_DAYHEATINFO.FOREIGNID = GG_HRZXX.ID;

转换为

 SELECT * FROM
(select
GG_HRZXX.HRZMC AS hrzmc,
ENERGY_DAYHEATINFO.NIANDU AS niandu,
ENERGY_DAYHEATINFO.RQDATE AS rqdate,
ENERGY_DAYHEATINFO.Sjrnh AS sjrnh
from ENERGY_DAYHEATINFO,GG_HRZXX
where ENERGY_DAYHEATINFO.FOREIGNID = GG_HRZXX.ID) a
pivot(MAX(SJRNH) FOR rqdate IN (TO_DATE('2017/11/16','YYYY/MM/DD'),TO_DATE('2017/11/15','YYYY/MM/DD'),
 TO_DATE('2018/11/15','YYYY/MM/DD'),TO_DATE('2018/11/16','YYYY/MM/DD')));

但是in（这里边的内容不能写死了），所以就需要用到存储过程去拼接sql：

这是测试代码块：

-- Created on 2020/6/3 by 7HELL 
declare 
v_sql varchar2(32767) := '';
cursor cur is  select distinct to_date(to_char(a.rqdate,'yyyy/mm/dd'),'yyyy/mm/dd') as rqdate from ENERGY_DAYHEATINFO a,GG_HRZXX b where a.foreignid = b.id;
begin
  -- Test statements here
    v_sql:='select * from
(select
GG_HRZXX.HRZMC AS hrzmc,
ENERGY_DAYHEATINFO.NIANDU AS niandu,
ENERGY_DAYHEATINFO.RQDATE AS rqdate,
ENERGY_DAYHEATINFO.Sjrnh AS sjrnh,
ENERGY_DAYHEATINFO.Sfmj as sfmj
from ENERGY_DAYHEATINFO,GG_HRZXX where ENERGY_DAYHEATINFO.FOREIGNID = GG_HRZXX.ID) a
  pivot(MAX(SJRNH) FOR rqdate IN (';
  for rqdate in cur
    loop
      v_sql:=v_sql ||''''||rqdate.rqdate||''''||','; 
      DBMS_OUTPUT.PUT_LINE(V_SQL);
     END LOOP;
     select substr(v_sql,1,length(v_sql)-1) into v_sql from dual;
     v_sql:=v_sql||'))';
      DBMS_OUTPUT.PUT_LINE(V_SQL);
end;

调试DBMS输出的结果为：

(select
GG_HRZXX.HRZMC AS hrzmc,
ENERGY_DAYHEATINFO.NIANDU AS niandu,
ENERGY_DAYHEATINFO.RQDATE AS rqdate,
ENERGY_DAYHEATINFO.Sjrnh AS sjrnh,
ENERGY_DAYHEATINFO.Sfmj as sfmj
from ENERGY_DAYHEATINFO,GG_HRZXX where ENERGY_DAYHEATINFO.FOREIGNID = GG_HRZXX.ID) a
  pivot(MAX(SJRNH) FOR rqdate IN ('15-11月-17','16-11月-17','15-11月-18','16-11月-18'))

but...............................................

这个日期格式不是我想要的，再研究一下