行列转换--普通

来源：互联网发布：帝国源码安装教程编辑：程序博客网时间：2024/04/28 13:44

/*行列转换--普通*/
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 胖子*/