oracle中行转列的sql语句动态

create table T_TEST
(
  CITY        VARCHAR2(255) not null,
  YEAR        NUMBER(4) not null,
  MONTH       NUMBER(2) not null,
  SELL_AMOUNT NUMBER(26,2)
)

comment on table T_TEST
  is '各月世联在主要核心城市的销售金额';
comment on column T_TEST.CITY
  is '城市';
comment on column T_TEST.SELL_AMOUNT
  is '比如0.42代表42%';


insert into T_TEST (CITY, YEAR, MONTH, SELL_AMOUNT)
values ('深圳', 2010, 5, 1.2);
insert into T_TEST (CITY, YEAR, MONTH, SELL_AMOUNT)
values ('东莞', 2010, 5, .8);
insert into T_TEST (CITY, YEAR, MONTH, SELL_AMOUNT)
values ('佛山', 2010, 5, .9);
insert into T_TEST (CITY, YEAR, MONTH, SELL_AMOUNT)
values ('汕头', 2010, 5, .22);
insert into T_TEST (CITY, YEAR, MONTH, SELL_AMOUNT)
values ('长沙', 2010, 5, .28);
insert into T_TEST (CITY, YEAR, MONTH, SELL_AMOUNT)
values ('深圳', 2010, 4, 1.1);
insert into T_TEST (CITY, YEAR, MONTH, SELL_AMOUNT)
values ('东莞', 2010, 4, .8);
insert into T_TEST (CITY, YEAR, MONTH, SELL_AMOUNT)
values ('佛山', 2010, 4, .9);
insert into T_TEST (CITY, YEAR, MONTH, SELL_AMOUNT)
values ('广州', 2010, 4, .67);
insert into T_TEST (CITY, YEAR, MONTH, SELL_AMOUNT)
values ('汕头', 2010, 4, .22);
insert into T_TEST (CITY, YEAR, MONTH, SELL_AMOUNT)
values ('长沙', 2010, 4, .28);
insert into T_TEST (CITY, YEAR, MONTH, SELL_AMOUNT)
values ('深圳', 2010, 3, .9);
insert into T_TEST (CITY, YEAR, MONTH, SELL_AMOUNT)
values ('佛山', 2010, 3, .9);
insert into T_TEST (CITY, YEAR, MONTH, SELL_AMOUNT)
values ('广州', 2010, 3, .67);
insert into T_TEST (CITY, YEAR, MONTH, SELL_AMOUNT)
values ('汕头', 2010, 3, .22);
insert into T_TEST (CITY, YEAR, MONTH, SELL_AMOUNT)
values ('长沙', 2010, 3, .28);
commit;


---存储过程生成行转列sql语句
create or replace procedure P_test1 is

    cursor   csr   is
        select   distinct   (city)   from   t_test;
    tmp_km   varchar2(400);
    str         varchar2(4000);
begin

/*select   t.year||t.month,
sum(decode(t.city, '深圳',t.sell_amount,0)) 深圳  ,
sum(decode(t.city, '佛山',t.sell_amount,0))   佛山,
sum(decode(t.city, '东莞',t.sell_amount,0))   东莞
from   t_test t
group   by   t.month,t.year
*/


    str   :=   'select   t.year||t.month ';
    open   csr;
      loop   fetch   csr   into   tmp_km;
    exit   when   csr%notfound;
    str   :=   str   ||   ',sum(decode(t.city, '   ||   chr(39)   ||   tmp_km   ||   chr(39)   ||
                  ',t.sell_amount,0))   '   ||   tmp_km;
end   loop;
str   :=   str   ||   '   from   t_test t   group   by   t.month,t.year';
dbms_output.put_line(str);



execute   immediate   str;

end P_test1;

//生成集合
create or replace procedure P_test1(p_Year NUMBER,p_Cursor OUT SYS_REFCURSOR) is

    cursor   csr   is
        select   distinct   (city)   from   t_test;
    tmp_km   varchar2(400);
    str         varchar2(4000);
begin

/*select   t.year||t.month,
sum(decode(t.city, '深圳',t.sell_amount,0)) 深圳  ,
sum(decode(t.city, '佛山',t.sell_amount,0))   佛山,
sum(decode(t.city, '东莞',t.sell_amount,0))   东莞
from   t_test t
group   by   t.month,t.year
*/


    str   :=   'select   t.year||t.month ';
    open   csr;
      loop   fetch   csr   into   tmp_km;
    exit   when   csr%notfound;
    str   :=   str   ||   ',sum(decode(t.city, '   ||   chr(39)   ||   tmp_km   ||   chr(39)   ||
                  ',t.sell_amount,0))   '   ||   tmp_km;
end   loop;
str   :=   str   ||   '   from   t_test t  where t.year='||p_Year||'  group   by   t.month,t.year';
dbms_output.put_line(str);

open p_Cursor for str;

exception

when others then

Dbms_Output.Put_Line('error');

close p_Cursor;


end P_test1;