/*行列转换--普通*/
if exists (select * from sysobjects where id=object_id('a') and sysstat & 0xf = 3)
drop table dbo.a
create table dbo.a(
Name1 varchar(20) not null,
Subject varchar(10) null,
Result varchar(3) null,
)
GO
insert a (Name1,Subject,Result) values ('张三','语文','80')
insert a (Name1,Subject,Result) values ('张三','数学','90')
insert a (Name1,Subject,Result) values ('张三','物理','85')
insert a (Name1,Subject,Result) values ('李四','语文','85')
insert a (Name1,Subject,Result) values ('李四','数学','92')
insert a (Name1,Subject,Result) values ('李四','物理','82')
select * from a
declare @sql varchar(4000)
set @sql = 'select Name1 as 姓名'
select @sql=@sql+',sum(case Subject when '''+Subject+''' then Result else 0 end) as '+Subject
from (select distinct Subject from a) as cj
select @sql = @sql+' from a group by Name1'
print(@sql)
exec(@sql)
/*win2000 server+sql server 2000 胖子*/