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


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

    具体示例如下:

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

 

本文作者:



相关阅读:
如何部署Exchange 2000路由和存储(3)
Linux操作系统的内存管理指南
“由于这台计算机没有终端服务器客户端访问许可证远程会话终段”的解决方法
ASP.NET验证控件详解
jquery实现select的美化
Linux下调整网卡的工作模式
常用硬件驱动及硬件工具下载
发挥语言的威力--融合PHP与ASP
提升你网站水平的jQuery插件集合推荐
抽取10万条数据,想起GetRows()
从一道面试题谈linux下fork的运行机制
JS脚本混淆、加密讨论
5.4 使用JSLint完成JavaScript语法检查
dedecms企业会员采集模型
快速导航

Copyright © 2016 phpStudy |