横向纵向都有 if object_id('test') is not null drop table test 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) /* 姓名 数学 英语 语文 小计 张三010060160 王五758057212 李四70080150 合计145180197522 */ godrop table Test
if object_id('test') is not null drop table test
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)
/*
姓名 数学 英语 语文 小计
张三010060160
王五758057212
李四70080150
合计145180197522
*/
godrop table Test