讲解数据库中行级转换成列级的实际应用

create table score
(
   Name    varchar(10),
   Subject varchar(10),
   Result int
)
go
 
-- Insert some data to score table
insert into score(Name , Subject , Result) values("Jason" , "Chinese" , 74)
insert into score(Name , Subject , Result) values("Jason" , "Math" , 83)
insert into score(Name , Subject , Result) values("Jason" , "Physic" , 93)
insert into score(Name , Subject , Result) values("Bosco" , "Chinese" , 74)
insert into score(Name , Subject , Result) values("Bosco" , "Math" , 84)
insert into score(Name , Subject , Result) values("Bosco" , "Physic" , 94)
go
 
-- useing PIVOT operator
SELECT * FROM
(    
    select Name,Subject,Result from score
) as X
PIVOT
(    
   Sum(Result) FOR Subject IN ([Chinese],[Math],[Physic])
) AS PVT
 
-- useing static SQL. There only are [Chinese],
[Math],[Physic] values in subject field
select Name,
 sum(case Subject when "Chinese" then Result else 0 end) as [Chinese],
 sum(case Subject when "Math" then Result else 0 end) as [Math],
 sum(case Subject when "Physic" then Result else 0 end) as [Physic]
from score
group by Name
 
-- useing dynamic SQL. There may be some other 
values in subject field,don"t limited to [Chinese],[Math],[Physic]
declare @sql varchar(2000)
set @sql = "select Name"
select @sql = @sql + ", sum(case Subject when 
""" + Subject + """ then Result else 0 end) [" + Subject + "]"
from (select distinct Subject from score) as X
set @sql = @sql + " from score group by Name"
exec(@sql)