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

王朝other·作者佚名  2008-06-01
窄屏简体版  字體: |||超大  

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

首先,我们需要建立一个表,详细内容请参考下文:

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)

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有  導航