Oracle认证:数据库行级转换列级案例
http://itpx.eol.cn 来源: 作者:网络转载 2011-09-14 大 中 小
数据库中行级转换成列级的应用案例:
具体示例如下:
—— 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)
免责声明:
① 凡本站注明“稿件来源:中国教育在线”的所有文字、图片和音视频稿件,版权均属本网所有,任何媒体、网站或个人未经本网协议授权不得转载、链接、转贴或以其他方式复制发表。已经本站协议授权的媒体、网站,在下载使用时必须注明“稿件来源:中国教育在线”,违者本站将依法追究责任。
② 本站注明稿件来源为其他媒体的文/图等稿件均为转载稿,本站转载出于非商业性的教育和科研之目的,并不意味着赞同其观点或证实其内容的真实性。如转载稿涉及版权等问题,请作者在两周内速来电或来函联系。