oracle查询小例

select s_cid,
max(case when num = 1 then zhangwuny else null end) as 账务年月1,
max(case when num = 1 then i_xiaofeil else null end) as 消费量1,
max(case when num = 1 then i_bencicm else null end) as 本次抄吗1,
max(case when num = 1 then d_bencicbrq else null end) as 本次抄表日期1,
max(case when num = 2 then zhangwuny else null end) as 账务年月2,
max(case when num = 2 then i_xiaofeil else null end) as 消费量2,
max(case when num = 2 then i_bencicm else null end) as 本次抄吗2,
max(case when num = 2 then d_bencicbrq else null end) as 本次抄表日期2,
max(case when num = 3 then zhangwuny else null end) as 账务年月3,
max(case when num = 3 then i_xiaofeil else null end) as 消费量3,
max(case when num = 3 then i_bencicm else null end) as 本次抄吗3,
max(case when num = 3 then d_bencicbrq else null end) as 本次抄表日期3,
max(case when num = 4 then zhangwuny else null end) as 账务年月4,
max(case when num = 4 then i_xiaofeil else null end) as 消费量4,
max(case when num = 4 then i_bencicm else null end) as 本次抄吗4,
max(case when num = 4 then d_bencicbrq else null end) as 本次抄表日期4,
max(case when num = 5 then zhangwuny else null end) as 账务年月5,
max(case when num = 5 then i_xiaofeil else null end) as 消费量5,
max(case when num = 5 then i_bencicm else null end) as 本次抄吗5,
max(case when num = 5 then d_bencicbrq else null end) as 本次抄表日期5
from (
select c.*,d.i_xiaofeil,d.i_bencicm,d.d_bencicbrq,
row_number() over(PARTITION BY c.s_cid order by c.zhangwuny asc) as num
from(
select a.COLUMN_VALUE zhangwuny,b.s_cid from table(getYM(201201,5)) a,(select s_cid from kg_yonghuxx where s_st='39' and s_ch='6015')b)
c
left join cb_chaobiaosj d on c.zhangwuny=d.i_zhangwuny and c.s_cid=d.s_cid )
group by s_cid;

declare v_sel varchar2(32765);
begin
for i in 1..5 loop
v_sel:=v_sel||'max(case when num = '||i||' then zhangwuny else null end) as 账务年月'||i||','||chr(10);
v_sel:=v_sel||'max(case when num = '||i||' then i_xiaofeil else null end) as 消费量'||i||','||chr(10);
v_sel:=v_sel||'max(case when num = '||i||' then i_bencicm else null end) as 本次抄吗'||i||','||chr(10);
v_sel:=v_sel||'max(case when num = '||i||' then d_bencicbrq else null end) as 本次抄表日期'||i||','||chr(10);
end loop;
print_proc(v_sel);
--dbms_output.put_line(v_sel);
end;

转载于:https://www.cnblogs.com/smart-liu/archive/2013/05/22/3092614.html