/*
姓名     语文    数学    物理    英语    化学    生物    文科    理科   平均分    总分
------ ------- ------- ------- ------- ------- -------  ------- ------- ------- -------
李四     74      84      94      缺考    缺考    缺考    74      178    42        252
张三     74      83      93      白卷    缺考    88      162     176    56.33     338
*/

create table tblSubjectItem
(
    siID int,
    siName varchar(20)
)

insert into tblSubjectItem(siID, siName)values(1, '语文');
insert into tblSubjectItem(siID, siName)values(2, '数学');
insert into tblSubjectItem(siID, siName)values(3, '物理');
insert into tblSubjectItem(siID, siName)values(4, '英语');
insert into tblSubjectItem(siID, siName)values(5, '化学');
insert into tblSubjectItem(siID, siName)values(6, '生物');

create table tblScoreResult
(
   [Name]    varchar(10) ,
   subjectID int ,
   Score  int                -- 0 白卷; -1 or null 缺考
)

insert into tblScoreResult([Name] , subjectID , Score) values('张三' , 1 , 74)
insert into tblScoreResult([Name] , subjectID , Score) values('张三' , 2 , 83)
insert into tblScoreResult([Name] , subjectID , Score) values('张三' , 3 , 93)
insert into tblScoreResult([Name] , subjectID , Score) values('张三' , 4 , 0)
insert into tblScoreResult([Name] , subjectID , Score) values('张三' , 5 , -1)
insert into tblScoreResult([Name] , subjectID , Score) values('张三' , 6 , 88)
insert into tblScoreResult([Name] , subjectID , Score) values('李四' , 1 , 74)
insert into tblScoreResult([Name] , subjectID , Score) values('李四' , 2 , 84)
insert into tblScoreResult([Name] , subjectID , Score) values('李四' , 3 , 94)


declare @sql varchar(6000)

select @sql=
'with t as
(select c.name,c.siname,isnull(d.score,-1) score
from
(select a.name,b.siname,b.siID from
(select distinct Name from tblScoreResult) a
cross join tblSubjectItem b) c
left join tblScoreResult d on c.name=d.name and c.siID=d.subjectID
)
select name,'

select @sql=@sql+
'(select case when score=-1 then ''缺考'' when score=0 then ''白卷'' 
else cast(score as varchar(3)) end from t t2 where t2.name=t.name and t2.siname='''+siName+''') '''+siName+''','
from tblSubjectItem

select @sql=@sql
+'sum(case when siname in (''语文'',''英语'',''生物'') then score else 0 end) ''文科'', '
+'sum(case when siname in (''数学'',''物理'',''化学'') then score else 0 end) ''理科'', '
+'sum(case when score=-1 then 0 else score end)/(select count(*) from tblSubjectItem) ''平均分'','
+'sum(case when score=-1 then 0 else score end) ''总分'' from t group by name '

exec(@sql)

name       语文   数学   物理   英语   化学   生物   文科     理科          平均分         总分
---------- ---- ---- ---- ---- ---- ---- ----------- ----------- ----------- -----------
李四         74   84   94   缺考   缺考   缺考     72          177         42          252
张三         74   83   93   白卷   缺考   88      162         175         56          338