declare @sql varchar(8000)
set @sql = 'select name,'
select @sql = @sql + 'sum(case subject when '''+subject+'''
then source else 0 end) as '''+subject+''','
from (select distinct subject from test) as aselect @sql = left(@sql,len(@sql)-1) + ',sum(source) as 合计 from test group by name'
exec(@sql)
go
set @sql = 'select name,'
select @sql = @sql + 'sum(case subject when '''+subject+'''
then source else 0 end) as '''+subject+''','
from (select distinct subject from test) as aselect @sql = left(@sql,len(@sql)-1) + ',sum(source) as 合计 from test group by name'
exec(@sql)
go
set @sql = 'select name'
select @sql = @sql + ',sum(case subject when '''+subject+'''
then source else 0 end) as ['+subject+']'
from (select distinct subject from test) as aselect @sql = @sql + ',sum(source) as 合计 from test group by name'
exec(@sql)
go
go
select '张三' as [name], '语文' as subject, 60 as Source
into test
union select '李四', '数学', 70
union select '王五', '英语', 80
union select '王五', '数学', 75
union select '王五', '语文', 57
union select '李四', '语文', 80
union select '张三', '英语', 100
godeclare @sql varchar(8000)
set @sql = 'select name as 姓名, '
select @sql = @sql + 'sum(case subject when ''' + subject+ ''' then source else 0 end) as '''+subject+''','
from (select distinct subject from test) as a
set @sql = @sql + ' sum(source) as 小计 from test group by name '
+ 'union select ''合计'', '
select @sql = @sql + 'sum(case subject when ''' + subject+ ''' then source else 0 end),'
from (select distinct subject from test) as a
set @sql = @sql + ' sum(source) as 合计 from test order by name desc'exec(@sql)
/*
姓名 数学 英语 语文 小计
张三 0 100 60 160
王五 75 80 57 212
李四 70 0 80 150
合计 145 180 197 522
*/
godrop table Test