行转列的4种方法(有完整例)

1.0
有两个表,例如:
table1:
姓名   性别   年龄
张三   男     22
李四   男     19

table2:
姓名  科目  成绩
张三  数学  88
张三  语文  78
李四  数学  79
李四  语文  91

我想查询的结果是这样的

姓名  数学  语文
张三   88    78
李四   79    91
===========
(1)select a.姓名,sum(decode(b.科目,'数学',成绩) as 数学,
              sum(decode(b.科目,'语文',成绩) as 语文
from table1 a,table2 b where a.姓名=b.姓名
group by a.姓名

(2)declare v_sql varchar2(4000):='select a.姓名,';
cursor cur_name is select distinct 科目 from table2;
begin
 for c in cur_name loop
   v_sql:=v_sql||'max(decode(b.科目,'||c.科目||',b.成绩))'''||c.科目||''',';
 end loop;
v_sql:=substr(v_sql,1,length(v_sql)-1)||' from table1 a,table2 b where a.姓名=b.姓名
group by a.姓名';
execute immediate v_sql;

(3)create table table1( cname varchar2(100),csex varchar2(100),cage int )
insert into table1
select '张三','男',22 from dual
union
select '李四','男',19 from dual;

create table table2(cname varchar2(100),km varchar2(100),cj int )
insert into table2
select '张三','数学',88 from dual
union
select '张三','语文',78 from dual
union
select '李四','数学',79 from dual
union
select '李四','语文',91 from dual;
==
select
a.cname,
max(decode(b.km,'数学',b.cj)) as 数学,
max(decode(b.km,'语文',b.cj)) as 语文
from
table1 a,table2 b
where
a.cname=b.cname
group by
a.cname

(4)select  a.stu_name,
(select b.stu_score  from dev.table2 b where b.stu_name=a.stu_name and b.stu_course='数学') math,
(select b.stu_score  from dev.table2 b where b.stu_name=a.stu_name and b.stu_course='语文') chinese
from dev.table1 a