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;