【赛迪网:技术社区整理】数据库中行级转换成列级的应用案例:
具体示例如下:
-- 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)
闽公网安备 35060202000074号