【赛迪网：技术社区整理】数据库中行级转换成列级的应用案例：

具体示例如下：

-- 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)