| 導購 | 订阅 | 在线投稿
分享
 
 
 

講解數據庫中行級轉換成列級的實際應用

來源:互聯網  2008-06-01 02:15:19  評論

講解數據庫中行級轉換成列級的實際應用:

首先,我們需要建立一個表,詳細內容請參考下文:

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)

講解數據庫中行級轉換成列級的實際應用: 首先,我們需要建立一個表,詳細內容請參考下文: 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- 王朝網路 版權所有