select 学生1,语文1,数学1 from tb
union all
select 学生2,语文2,数学2 from tb
union all
select 学生3,语文3,数学3 from tb
union all
select '总分',语文1+语文2+语文3,数学1+数学2+数学3 from tb

------解决方案--------------------
SQL code
--构建示例数据 
create table #ta(学生1 varchar(10), 学生2 varchar(10),  学生3 varchar(10), 
 语文1 int, 语文2 int, 语文3 int, 数学1 int, 数学2 int, 数学3 int)
insert into #ta select '贾某', '艺谋', '并某', 69, 78, 90, 56, 85, 89

--解决方案演示
;with CET1 as
(select 姓名 as 学生, sum(语文)语文,sum(数学) 数学 
from(select *,left(科目,2) as 科目2 from #ta 
                        unpivot (分数 for 科目 in([语文1],[语文2],[语文3],[数学1],[数学2],[数学3]))u
                        unpivot (姓名 for 学生 in([学生1],[学生2],[学生3]) )u1
where right(科目,1) = right(学生,1))a
pivot(sum(分数)  for 科目2 in([语文],[数学]) )p
group by 姓名
)select *,''  from CET1
union
select '总分',sum(语文),sum(数学),CAST(sum(语文+数学) AS VARCHAR(10))from cet1
/*
学生         语文          数学          
---------- ----------- ----------- ----------
并某         90          89          
贾某         69          56          
艺谋         78          85          
总分         237         230         467

(4 行受影响)
*/

------解决方案--------------------
假设你有很多学生，以下是动态语句
SQL code
--构建示例数据 
create TAble #TA(学生1 varchar(10), 学生2 varchar(10),  学生3 varchar(10), 
 语文1 int, 语文2 int, 语文3 int, 数学1 int, 数学2 int, 数学3 int)
insert into #TA select '贾某', '艺谋', '并某', 69, 78, 90, 56, 85, 89

DECLARE @SQL VARCHAR(MAX),@sql1 varchar(max),@sql2 varchar(max),@sql3 varchar(max),@sql4 varchar(max),@sql5 varchar(max)
select @sql1 = isnull(@sql1,'') +'['+ left(name,2) +'],' 
        from (select distinct left(name,2) as name from tempdb.sys.syscolumns  where id =  object_id('tempdb..#TA') and name not like '学生%')a
set @sql1 = left(@sql1,len(@sql1)-1)
select @sql2 = isnull(@sql2,'') +'['+ left(name,2) +']+' 
        from (select distinct left(name,2) as name from tempdb.sys.syscolumns  where id =  object_id('tempdb..#TA') and name not like '学生%')a
set @sql2 = left(@sql2,len(@sql2)-1)
select @sql3 = isnull(@sql3,'') +'sum(['+ left(name,2) +'])'+ left(name,2) +',' 
        from (select distinct left(name,2) as name from tempdb.sys.syscolumns  where id =  object_id('tempdb..#TA') and name not like '学生%')a
set @sql3 = left(@sql3,len(@sql3)-1)
select @sql4 = isnull(@sql4,'') +'['+ name +'],' from tempdb.sys.syscolumns  where id =  object_id('tempdb..#TA') and name like '学生%'
set @sql4 = left(@sql4,len(@sql4)-1)
select @sql5 = isnull(@sql5,'') +'['+ name +'],' from tempdb.sys.syscolumns  where id =  object_id('tempdb..#TA') and name not like '学生%'
set @sql5 = left(@sql5,len(@sql5)-1)

--解决方案演示
set @SQL = '
;with CET1 as
(select 姓名 as 学生, '+@sql3+' 
from(select *,left(科目,2) as 科目2 from #TA 
                        unpivot (分数 for 科目 in('+@sql5+'))u
                        unpivot (姓名 for 学生 in('+@sql4+') )u1
where right(科目,1) = right(学生,1))a
pivot(sum(分数)  for 科目2 in('+@sql1+') )p
group by 姓名
)select *,''''  from CET1
union
select ''总分'',sum(语文),sum(数学),CAST(sum('+@sql2+') AS VARCHAR(10))from cet1 '
PRINT @SQL
EXEC(@SQL)
/*
学生         语文          数学          
---------- ----------- ----------- ----------
并某         90          89          
贾某         69          56          
艺谋         78          85          
总分         237         230         467

(4 行受影响)
*/