关于SQLServer 中行列互转的实例说明

这几天在做一个招标系统中审批模块,其中关于报价信息这块,用到了pivot和unpivot来实现数据的行列互转,下面简单介绍一下,实际案例,便于回忆和记录相关的条件下使用的情况。pivot 与 unpivot 函数是SQL2005新提供的2个函数,PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式,并在必要时对最终输出中所需的任何其余列值执行聚合。UNPIVOT 与 PIVOT 执行相反的操作,将表值表达式的列转换为列值。

      下面我通过PIVOT 来阐述整个函数的使用:

语法

SELECT <非透视的列>,

    [第一个透视的列] AS <列名称>,

     [第二个透视的列] AS <列名称>, ...

[最后一个透视的列] AS <列名称>,

FROM(<生成数据的 SELECT 查询>)

 AS <源查询的别名>

PIVOT(

<聚合函数>(<要聚合的列>)

FOR

[<包含要成为列标题的值的列>]

IN ( [第一个透视的列], [第二个透视的列], ... [最后一个透视的列])

) AS <透视表的别名>

实例: select PRICE,Sup_Name,QUOT_ITEM1,QUOT_ITEM3,QUANTITY from  Q2B_QUOT_ITEM where <相关条件筛选>

执行显示:

       PRICE Sup_Name QUOT_ITEM1   QUOT_ITEM3 QUANTITY          342 测试供应商1 5H52921000088 剪刀              4.000          422 测试供应商1 5H48911000023 黑色水笔芯     2.000          211 测试供应商1 5H57161000002 计算器           2.000          324 测试供应商2 5H52921000088 剪刀              4.000          342 测试供应商2 5H48911000023 黑色水笔芯      2.000          234 测试供应商2 5H57161000002 计算器           2.000          434 测试供应商3 5H52921000088 剪刀              4.000          232 测试供应商3 5H48911000023 黑色水笔芯     2.000         2432 测试供应商3 5H57161000002 计算器          2.000

发现正常情况下读取数据显示的是按照Sup_Name(供应商)作为列值显示。目前客户要求以物资为条件对各供应上报价进行汇总显示。接下来我们按照刚才提供的语法使用pivot来实现列转行。

sql:select* from (select PRICE,Sup_Name,QUOT_ITEM1,QUOT_ITEM3,QUANTITY from Q2B_QUOT_ITEM where <相关条件筛选> as <别名>

pivot (max(PRICE) for ord.Sup_Name in (测试供应商1,测试供应商3,测试供应商2)) b

显示:QUOT_ITEM1 QUOT_ITEM3 QUANTITY 测试供应商1 测试供应商3 测试供应商2       5H48911000023 黑色水笔芯     2.000          422          232          342       5H52921000088 剪刀              4.000          342          434          324        5H57161000002 计算器          2.000          211         2432          234

 使用pivot很简单的实现了列转行,对于类似的数据处理灰常灰常的实用,避免了使用case when 或者循环游标的复杂处理,大大提高了处理速度和代码整洁优雅。

注意事项: 1.对升级到 SQL Server 2005 或更高版本的数据库使用 PIVOT 和 UNPIVOT 时,必须将数据库的兼容级别设置为 90 或更高;

                2.UNPIVOT 将与 PIVOT 执行几乎完全相反的操作,将列转换为行,但是也不是完全的相同,PIVOT 会执行一次聚合,从而将多个可能的行合并为输出中的单个行。而 UNPIVOT 不会重现原始表值表达式的结果,因为行已经被合并了。另外,UNPIVOT 的输入中的空值不会显示在输出中,而在执行 PIVOT 操作之前,输入中可能有原始的空值。

               3.动态处理和静态处理不一样的地方在于列转行的数量。也就是FOR ... in 包含的数据。

 最后简单写一下:UNPIVOT用于将列名转为列值(即列转行)

语法:

UNPIVOT(

value_column

FOR  pivot_column

IN(<column_list>)

)

简单测试了一下,待以后有实际应用再把实际例子复制过来。不继续深入探讨了。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏文渊之博

PowerBI 引入时间智能

简介 Power BI Desktop -是一款由微软发布的自助式商业智能工具,功能强大、易于使用。其中还可以通过微软云连多个数据源并且使用数据源来创建可视化表...

3689
来自专栏大数据和云计算技术

索引技术简介

2.索引技术 索引是关系型数据库里的重要概念。总的来说,索引就是拿空间换时间。数据库技术和大数据技术会有一个融合的过程,除了前面讲到的B数索引、Hash索引等,...

5798
来自专栏FreeBuf

又见卡死图,竟然一个“小黑点”就能干掉QQ?

前一阵子QQ群里流行了一个东西,一段话加上一个黑点(表情),点击之后QQ就会卡死。(文内链接请点击“阅读原文”查看)

1355
来自专栏FSociety

Python使用itchat获取微信好友

最近发现了一个好玩的包itchat,通过调用微信网页版的接口实现收发消息,获取好友信息等一些功能,各位可以移步itchat项目介绍查看详细信息。

5072
来自专栏圆方圆学院精选

【许晓笛】EOS 数据库与持久化 API —— 架构

在 EOS 中,智能合约执行完毕后,所占用的内存会释放。程序中的所有变量都会丢失。如果智能合约里要持久地记录信息,比如游戏智能合约要记录每位用户游戏记录,本次合...

1894
来自专栏个人随笔

C# 操作 access 数据库

随笔: (1)   命名空间             using System.Data.OleDb; (2)   连接字符串             priv...

2975
来自专栏屈定‘s Blog

由需求而产生的一款db导出excel的工具

程序员最大的毛病可能就是懒,因为懒所以做出了许许多多提高自己工作效率的工具. 起因于我是商业开发,既然是商业项目避免不了各种数据统计,虽然公司有专门的数据平台,...

1665

视觉搜索和Neo4j的最后一公里

“ 最后一公里 ”是电信行业使用的一个术语,指系统为实际使用该系统的客户提供链接。就图形数据库而言,它指的是终端用户可以从图中提取有价值的信息和洞察力。我们...

1983
来自专栏IT大咖说

关于 Unicode 每个程序员应该知道的 5 件事

摘要 Unicode是一个令人难以置信的有用标准,它能使全世界的计算机、智能手机和智能手表以同样的方式显示相同的信息。不幸的是,它的复杂性使它成为了欺诈分子和恶...

2897
来自专栏Python数据科学

10行代码爬取全国所有A股/港股/新三板上市公司信息

摘要: 我们平常在浏览网页中会遇到一些表格型的数据信息,除了表格本身体现的内容以外,可能还想透过表格背后再挖掘些有意思或者有价值的信息。这时,可用python爬...

2012

扫码关注云+社区

领取腾讯云代金券