假设有张学生成绩表(tb)如下:
No Name Subject Result
01 张三 1 74
01 张三 2 83
01 张三 3 93
02 李四 1 74
02 李四 2 84
02 李四 3 94
1表示科目编号比如语文；2，表示数学；3表示物理
要求变成
学号 姓名 1 2 3 学生总分
---------- ----------- ----------- ----------- -----
02 李四 74 84 94 252
01 张三 74 83 93 250
合计 148 167 187 502
方法如下：
declare @sql varchar(8000)
DECLARE @TOTALSQL VARCHAR(8000)
set @sql = 'select No as [学号], Name as [姓名]'
SET @TOTALSQL = ' UNION ALL select '''',''合计'''
select @sql = @sql + ' , sum(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
, @TOTALSQL = @TOTALSQL + ' , sum(case Subject when ''' + Subject + ''' then Result else 0 end)'
from (select distinct Subject from tb) as a
set @sql = @sql + ' , ISNULL(sum(Result),0)[学生总分] from tb group by No,name '
set @TOTALSQL = @TOTALSQL + ' , ISNULL(sum(Result),0) from tb'
SET @sql=@sql+@TOTALSQL
--PRINT @sql
exec(@sql)
但是事实上有可能变成，学科没有进行排序
学号 姓名 1 3 2 学生总分
---------- ----------- ----------- ----------- -----
02 李四 74 94 84 252
01 张三 74 93 83 250
合计 148 187 167 502
------解决思路----------------------
你参考下
declare @sql varchar(8000)
DECLARE @TOTALSQL VARCHAR(8000)
set @sql = 'select No as [学号], Name as [姓名]'
SET @TOTALSQL = ' UNION ALL select '''',''合计'''
select @sql = @sql + ' , sum(case Subject when ''' + CAST(Subject AS VARCHAR(10)) + ''' then Result else 0 end) [' + CAST(Subject AS VARCHAR(10)) + ']'
, @TOTALSQL = @TOTALSQL + ' , sum(case Subject when ''' + CAST(Subject AS VARCHAR(10)) + ''' then Result else 0 end)'
from (select Subject from tb GROUP BY Subject) as a
set @sql = @sql + ' , ISNULL(sum(Result),0)[学生总分] from tb group by No,name '
set @TOTALSQL = @TOTALSQL + ' , ISNULL(sum(Result),0) from tb'
SET @sql=@sql+@TOTALSQL
--PRINT @sql
exec(@sql)