Create table test (name char(10),km char(10),cj int)
go
insert test values('张三','语文',80)
insert test values('张三','数学',86)
insert test values('张三','英语',75)
insert test values('李四','语文',78)
insert test values('李四','数学',85)
insert test values('李四','英语',78) declare @sql varchar(8000)
set @sql = 'select name'
select @sql = @sql + ',sum(case km when '''+km+''' then cj end) ['+km+']'
from (select distinct km from test) as a
select @sql = @sql+',sum(cj) 总分 from test group by name'
exec(@sql)
drop table test
go
insert test values('张三','语文',80)
insert test values('张三','数学',86)
insert test values('张三','英语',75)
insert test values('李四','语文',78)
insert test values('李四','数学',85)
insert test values('李四','英语',78) declare @sql varchar(8000)
set @sql = 'select name'
select @sql = @sql + ',sum(case km when '''+km+''' then cj end) ['+km+']'
from (select distinct km from test) as a
select @sql = @sql+',sum(cj) 总分 from test group by name'
exec(@sql)
drop table test
go
drop table test
Create table test (name char(10),km char(10),cj int)
go
insert test values('张三','语文',80)
insert test values('张三','数学',86)
insert test values('张三','英语',75)
insert test values('李四','语文',78)
insert test values('李四','数学',85)
insert test values('李四','英语',78)
insert test values('王五','英语',100)
select * from test
----执行此语句
declare @sql varchar(8000)
set @sql = 'select name'
select @sql = @sql + ',sum(case km when '''+km+''' then cj end) as ['+km+']'
from (select distinct km from test) as a
print @sql
select @sql = 'select a.*,b.总计 from ('+ @sql+' from test group by name)a
inner join (select name,总计=sum(cj) from test group by name) b on a.name = b.name'
exec(@sql)
select name,
sum(case km when '语文' then cj end) as [语文],
sum(case km when '数学' then cj end) as [数学],
sum(case km when '英语' then cj end) as [英语],
sum(cj) as 总分
from test group by name
sum(case km when '''+km+''' then cj end) as ['+km+']'
就没办法指定这些列的顺序了??