学生成绩同一行显示或者课程安排按照一周星期的显示

SELECT * FROM SCORE 
--成绩列表  oracle 
SELECT A.*, C.CNAME FROM SCORE A,COURSE C 
WHERE A.CNO = C.CNO


  SELECT ROWNUM, A.* FROM (     
SELECT      
        A.SNO ,
        S.SNAME,
        SUM(CASE   
            WHEN A.CNO = '3_245' THEN
                 A.DEGREE
             ELSE 0
             END)CZXI_SC,--操作系统
         SUM(CASE 
             WHEN A.CNO = '3_105' THEN 
               A.DEGREE
             ELSE  0
             END)JSJDL_SC,--计算机导论
         SUM(CASE 
              WHEN A.CNO = '6_166' THEN
                A.DEGREE
                ELSE 0
              END)SJDL_SC,--数据电路
         SUM(A.DEGREE)Z_SC--总成绩    
         FROM SCORE A , STUDENT S
         WHERE A.SNO = S.SNO
         GROUP BY A.SNO,S.SNAME
         ORDER BY Z_SC DESC --按照总成绩排名
         )A
        


-------------------------------------------------sqlserver
use adu_test
go
---教师课程表安排，类似于学生成绩的列发，但有些不同
create table course1(
id int ,
teacher varchar(50),
classno varchar(50),
data_time varchar(20)
)


insert into course1 values(1,'甲','1','2015-8-15');


insert into course1 values(2,'乙','2','2015-8-16');


insert into course1 values(3,'乙','4','2015-8-17');


select teacher ,
max(case when data_time = '2015-8-15' then classno+ '班' else ''end) '星期一',


max(case when data_time ='2015-8-16' then classno+'班' else ''end )'星期二',


max(case when data_time ='2015-8-17' then classno+'班'  else ''end) '星期三'


from course1 group by teacher