讲解数据库中行级转换成列级的应用案例

数据库中行级转换成列级的应用案例：

具体示例如下：

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