土狼的博客

-------------------------------------------------------------------------------------------------------
select user from dual
/

drop table tbl_org
/

create table tbl_org
(
 name varchar2(10),
 subject varchar2(10),
 grade number(3)
)
/

insert into tbl_org values('张三','语文',80);
insert into tbl_org values('张三','数学',86);
insert into tbl_org values('李四','语文',78);
insert into tbl_org values('李四','数学',85);
insert into tbl_org values('李四','英语',78);
commit
/

set serverout on;
drop table tbl_result;

declare
       col1   tbl_org.subject%type;
       sqlstr varchar2(2400);
       cursor c_try is select distinct subject from tbl_org;  
begin
     sqlstr:='';
    
     open c_try;
     loop  
            fetch c_try into col1;  
            exit when c_try%notfound;  
            sqlstr := sqlstr || ', max(decode(subject,'''||col1||''',grade,0)) as '||col1;
     end loop;
     close c_try;
    
     sqlstr:='create table tbl_result as select name '||sqlstr||' from tbl_org group by name'; 
     dbms_output.put_line('');
     dbms_output.put_line(sqlstr);
     execute immediate sqlstr;
end;
/

-------------------------------------------------------------------------------------------------------