数据库中行级转换成列级的经典应用
-- Firstly,we need to create a table
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)
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/YanJiangbo/archive/2008/03/04/2146944.aspx