sql行列转换

create table stu1
(
 sname varchar2(10),
 sshow   varchar2(20)
)




insert into stu1 (sname, sshow) values ('张三', '语文90');
insert into stu1 (sname, sshow) values ('张三', '数学50');
insert into stu1 (sname, sshow) values ('张三', '英语80');
insert into stu1 (sname, sshow) values ('李四', '语文91');
insert into stu1 (sname, sshow) values ('李四', '数学51');






select sname, allstr from 
(
  select sname,allstr,
  row_number() over(partition by sname order by sname,curr_level desc) ename_path_rank
  from (
         select sname,sshow,rank,level as curr_level,
         ltrim(sys_connect_by_path(sshow,','),',') allstr from  
         (
           select s1.sname,s1.sshow,row_number() over(partition by s1.sname order by s1.sname,s1.sshow) rank
           from stu1 s1 order by s1.sname,s1.sshow 
         ) connect by sname = prior sname and rank-1 = prior rank
       )
)
where ename_path_rank=1