遇到的一个面试题

create table student_test (id integer not null ,
      name varchar2(100),
      age integer,
      course varchar(100),
      score integer
      
);
insert into   student values('1','张三',14,'语文','80');
insert into   student values('2','张三',14,'数学','80');
insert into   student values('3','里斯',14,'语文','80');
insert into   student values('4','里斯',14,'数学','80');
insert into   student values('5','王我',14,'语文','80');
insert into   student values('6','王我',14,'数学','80');
insert into   student values('7','王六',14,'语文','80');

最终要转化为下面的表格

select * from ( select a.name as 姓名  ,decode(a.course,'语文',a.score) as 语文 ,decode(b.course,'数学',b.score) as 数学 from student a ,student b where a.name=b.name and a.id<>b.id )

  t    where t.语文 is not null