create table t1 (STUDNAME varchar2(20), COURSENAME varchar2(10), CSCORE number(3), USCORE number(5));

insert into t1 values ('jack','A',80,1);
insert into t1 values ('jack','B',60,5);
insert into t1 values ('jack','C',70,3);
insert into t1 values ('rose','A',95,1);
insert into t1 values ('rose','B',85,5);
insert into t1 values ('rose','C',75,3);
insert into t1 values ('lucy','A',45,1);
insert into t1 values ('lucy','B',55,5);
insert into t1 values ('lucy','C',50,3);
insert into t1 values ('lily','A',25,1);
insert into t1 values ('lily','B',15,5);
insert into t1 values ('lily','C',10,3);


select STUDNAME,
       sum(decode(COURSENAME,'A',CSCORE,0)) A, --判断每行的科目和成绩 根据姓名合并多行各科成绩
       sum(decode(COURSENAME,'B',CSCORE,0)) B,
       sum(decode(COURSENAME,'C',CSCORE,0)) C,
       sum(USCORE) USCORE
from t1
group by STUDNAME


   STUDNAME    A    B    C    USCORE
-------------------------------------------------
1    rose    95    85    75    9
2    jack    80    60    70    9
3    lucy    45    55    50    9
4    lily    25    15    10    9