点击这里给我发消息 点击这里给我发消息
首页 > 行业资讯 > Oracle>详细内容

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

添加时间:2010-1-5
    相关阅读: 案例 数据库 SQL

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

    具体示例如下:

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

 

本文作者:
咨询热线:020-85648757 85648755 85648616 0755-27912581 客服:020-85648756 0755-27912581 业务传真:020-32579052
广州市网景网络科技有限公司 Copyright◎2003-2008 Veelink.com. All Rights Reserved.
广州商务地址:广东省广州市黄埔大道中203号(海景园区)海景花园C栋501室
= 深圳商务地址:深圳市宝源路华丰宝源大厦606
研发中心:广东广州市天河软件园海景园区 粤ICP备05103322号 工商注册