使用 PIVOT 和 UNPIVOT

本贴最后更新于 2742 天前,其中的信息可能已经天翻地覆

可以使用 PIVOT 和 UNPIVOT 关系运算符将表值表达式更改为另一个表。PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式,并在必要时对最终输出中所需的任何其余列值执行聚合。UNPIVOT 与 PIVOT 执行相反的操作,将表值表达式的列转换为列值。

| 注意 |
|

对升级到 SQL Server 2005 或更高版本的数据库使用 PIVOT 和 UNPIVOT 时,必须将数据库的兼容级别设置为 90 或更高。有关如何设置数据库兼容级别的信息,请参阅 sp_dbcmptlevel (Transact-SQL)

|

PIVOT 提供的语法比一系列复杂的 SELECT...CASE 语句中所指定的语法更简单和更具可读性。有关 PIVOT 语法的完整说明,请参阅 FROM (Transact-SQL)

以下是带批注的 PIVOT 语法。

SELECT < 非透视的列 >,

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

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

...

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

FROM

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

AS <源查询的别名>

PIVOT

(

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

FOR

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

IN ( [第一个透视的列], [第二个透视的列],

... [最后一个透视的列])

) AS < 透视表的别名 >

< 可选的 ORDER BY 子句 >;

简单 PIVOT 示例

下面的代码示例生成一个两列四行的表。

USE AdventureWorks2008R2 ;
GO
SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost
FROM Production.Product
GROUP BY DaysToManufacture;

下面是结果集:

DaysToManufacture AverageCost

0 5.0885

1 223.88

2 359.1082

4 949.4105

没有定义 DaysToManufacture 为 3 的产品。

以下代码显示相同的结果,该结果经过透视以使 DaysToManufacture 值成为列标题。提供一个列表示三 [3] 天,即使结果为 NULL。

-- Pivot table with one row and five columns
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days,
[0], [1], [2], [3], [4]
FROM
(SELECT DaysToManufacture, StandardCost
FROM Production.Product) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;

下面是结果集:

Cost_Sorted_By_Production_Days 0 1 2 3 4

AverageCost 5.0885 223.88 359.1082 NULL 949.4105

复杂 PIVOT 示例

可能会用到 PIVOT 的常见情况是:需要生成交叉表格报表以汇总数据。例如,假设需要在 AdventureWorks2008R2 示例数据库中查询 PurchaseOrderHeader 表以确定由某些特定雇员所下的采购订单数。以下查询提供了此报表(按供应商排序)。

USE AdventureWorks2008R2;
GO
SELECT VendorID, [250] AS Emp1, [251] AS Emp2, [256] AS Emp3, [257] AS Emp4, [260] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [250], [251], [256], [257], [260] )
) AS pvt
ORDER BY pvt.VendorID;

以下为部分结果集。

VendorID Emp1 Emp2 Emp3 Emp4 Emp5

1492 2 5 4 4 4

1494 2 5 4 5 4

1496 2 4 4 5 5

1498 2 5 4 4 4

1500 3 4 4 5 4

将在 EmployeeID 列上透视此嵌套 select 语句返回的结果。

SELECT PurchaseOrderID, EmployeeID, VendorID
FROM PurchaseOrderHeader;

这意味着 EmployeeID 列返回的唯一值自行变成了最终结果集中的字段。因此,在透视子句中指定的每个 EmployeeID 号都有相应的一列:在本例中为雇员 164、198、223、231 和 233。PurchaseOrderID 列作为值列,将根据此列对最终输出中返回的列(称为分组列)进行分组。在本例中,通过 COUNT 函数聚合分组列。请注意,将显示一条警告消息,指出为每个雇员计算 COUNT 时未考虑显示在 PurchaseOrderID 列中的任何空值。

| 重要提示 |
|

如果聚合函数与 PIVOT 一起使用,则计算聚合时将不考虑出现在值列中的任何空值。

|

UNPIVOT 将与 PIVOT 执行几乎完全相反的操作,将列转换为行。假设以上示例中生成的表在数据库中存储为 pvt,并且您需要将列标识符 Emp1、Emp2、Emp3、Emp4 和 Emp5 旋转为对应于特定供应商的行值。这意味着必须标识另外两个列。包含要旋转的列值(Emp1、Emp2...)的列将被称为 Employee,将保存当前位于待旋转列下的值的列被称为 Orders。这些列分别对应于 Transact-SQL 定义中的 pivot_column 和 value_column。以下为该查询。

--Create the table and insert values as portrayed in the previous example.
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5 int);
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4);
INSERT INTO pvt VALUES (2,4,1,5,5,5);
INSERT INTO pvt VALUES (3,4,3,5,4,4);
INSERT INTO pvt VALUES (4,4,2,5,5,4);
INSERT INTO pvt VALUES (5,5,1,5,5,5);
GO
--Unpivot the table.
SELECT VendorID, Employee, Orders
FROM
(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
FROM pvt) p
UNPIVOT
(Orders FOR Employee IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;
GO

以下为部分结果集。

VendorID Employee Orders


1 Emp1 4

1 Emp2 3

1 Emp3 5

1 Emp4 4

1 Emp5 4

2 Emp1 4

2 Emp2 1

2 Emp3 5

2 Emp4 5

2 Emp5 5

...

请注意,UNPIVOT 并不完全是 PIVOT 的逆操作。PIVOT 会执行一次聚合,从而将多个可能的行合并为输出中的单个行。而 UNPIVOT 不会重现原始表值表达式的结果,因为行已经被合并了。另外,UNPIVOT 的输入中的空值不会显示在输出中,而在执行 PIVOT 操作之前,输入中可能有原始的空值。

AdventureWorks2008R2 示例数据库中的 Sales.vSalesPersonSalesByFiscalYears 视图将使用 PIVOT 返回每个销售人员在每个会计年度的总销售额。若要在 SQL Server Management Studio 中编写视图脚本,请在“对象资源管理器”中,在“视图”文件夹下找到 AdventureWorks2008R2 数据库对应的视图。右键单击该视图名称,再选择“编写视图脚本为”。

相关帖子

欢迎来到这里!

我们正在构建一个小众社区,大家在这里相互信任,以平等 • 自由 • 奔放的价值观进行分享交流。最终,希望大家能够找到与自己志同道合的伙伴,共同成长。

注册 关于
请输入回帖内容 ...

推荐标签 标签

  • 阿里巴巴

    阿里巴巴网络技术有限公司(简称:阿里巴巴集团)是以曾担任英语教师的马云为首的 18 人,于 1999 年在中国杭州创立,他们相信互联网能够创造公平的竞争环境,让小企业通过创新与科技扩展业务,并在参与国内或全球市场竞争时处于更有利的位置。

    43 引用 • 221 回帖 • 129 关注
  • GitLab

    GitLab 是利用 Ruby 一个开源的版本管理系统,实现一个自托管的 Git 项目仓库,可通过 Web 界面操作公开或私有项目。

    46 引用 • 72 回帖
  • 锤子科技

    锤子科技(Smartisan)成立于 2012 年 5 月,是一家制造移动互联网终端设备的公司,公司的使命是用完美主义的工匠精神,打造用户体验一流的数码消费类产品(智能手机为主),改善人们的生活质量。

    4 引用 • 31 回帖
  • iOS

    iOS 是由苹果公司开发的移动操作系统,最早于 2007 年 1 月 9 日的 Macworld 大会上公布这个系统,最初是设计给 iPhone 使用的,后来陆续套用到 iPod touch、iPad 以及 Apple TV 等产品上。iOS 与苹果的 Mac OS X 操作系统一样,属于类 Unix 的商业操作系统。

    84 引用 • 139 回帖 • 1 关注
  • WebClipper

    Web Clipper 是一款浏览器剪藏扩展,它可以帮助你把网页内容剪藏到本地。

    3 引用 • 9 回帖 • 2 关注
  • Netty

    Netty 是一个基于 NIO 的客户端-服务器编程框架,使用 Netty 可以让你快速、简单地开发出一个可维护、高性能的网络应用,例如实现了某种协议的客户、服务端应用。

    49 引用 • 33 回帖 • 18 关注
  • abitmean

    有点意思就行了

    30 关注
  • 面试

    面试造航母,上班拧螺丝。多面试,少加班。

    325 引用 • 1395 回帖
  • Sphinx

    Sphinx 是一个基于 SQL 的全文检索引擎,可以结合 MySQL、PostgreSQL 做全文搜索,它可以提供比数据库本身更专业的搜索功能,使得应用程序更容易实现专业化的全文检索。

    1 引用 • 209 关注
  • Ant-Design

    Ant Design 是服务于企业级产品的设计体系,基于确定和自然的设计价值观上的模块化解决方案,让设计者和开发者专注于更好的用户体验。

    17 引用 • 23 回帖
  • wolai

    我来 wolai:不仅仅是未来的云端笔记!

    2 引用 • 14 回帖
  • Docker

    Docker 是一个开源的应用容器引擎,让开发者可以打包他们的应用以及依赖包到一个可移植的容器中,然后发布到任何流行的操作系统上。容器完全使用沙箱机制,几乎没有性能开销,可以很容易地在机器和数据中心中运行。

    490 引用 • 916 回帖 • 2 关注
  • 架构

    我们平时所说的“架构”主要是指软件架构,这是有关软件整体结构与组件的抽象描述,用于指导软件系统各个方面的设计。另外还有“业务架构”、“网络架构”、“硬件架构”等细分领域。

    142 引用 • 442 回帖
  • 倾城之链
    23 引用 • 66 回帖 • 139 关注
  • 笔记

    好记性不如烂笔头。

    308 引用 • 793 回帖
  • 设计模式

    设计模式(Design pattern)代表了最佳的实践,通常被有经验的面向对象的软件开发人员所采用。设计模式是软件开发人员在软件开发过程中面临的一般问题的解决方案。这些解决方案是众多软件开发人员经过相当长的一段时间的试验和错误总结出来的。

    200 引用 • 120 回帖 • 1 关注
  • HHKB

    HHKB 是富士通的 Happy Hacking 系列电容键盘。电容键盘即无接点静电电容式键盘(Capacitive Keyboard)。

    5 引用 • 74 回帖 • 465 关注
  • SQLite

    SQLite 是一个进程内的库,实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。SQLite 是全世界使用最为广泛的数据库引擎。

    5 引用 • 7 回帖
  • Spark

    Spark 是 UC Berkeley AMP lab 所开源的类 Hadoop MapReduce 的通用并行框架。Spark 拥有 Hadoop MapReduce 所具有的优点;但不同于 MapReduce 的是 Job 中间输出结果可以保存在内存中,从而不再需要读写 HDFS,因此 Spark 能更好地适用于数据挖掘与机器学习等需要迭代的 MapReduce 的算法。

    74 引用 • 46 回帖 • 561 关注
  • SpaceVim

    SpaceVim 是一个社区驱动的模块化 vim/neovim 配置集合,以模块的方式组织管理插件以
    及相关配置,为不同的语言开发量身定制了相关的开发模块,该模块提供代码自动补全,
    语法检查、格式化、调试、REPL 等特性。用户仅需载入相关语言的模块即可得到一个开箱
    即用的 Vim-IDE。

    3 引用 • 31 回帖 • 101 关注
  • Postman

    Postman 是一款简单好用的 HTTP API 调试工具。

    4 引用 • 3 回帖 • 3 关注
  • Shell

    Shell 脚本与 Windows/Dos 下的批处理相似,也就是用各类命令预先放入到一个文件中,方便一次性执行的一个程序文件,主要是方便管理员进行设置或者管理用的。但是它比 Windows 下的批处理更强大,比用其他编程程序编辑的程序效率更高,因为它使用了 Linux/Unix 下的命令。

    122 引用 • 73 回帖
  • 国际化

    i18n(其来源是英文单词 internationalization 的首末字符 i 和 n,18 为中间的字符数)是“国际化”的简称。对程序来说,国际化是指在不修改代码的情况下,能根据不同语言及地区显示相应的界面。

    8 引用 • 26 回帖
  • WebSocket

    WebSocket 是 HTML5 中定义的一种新协议,它实现了浏览器与服务器之间的全双工通信(full-duplex)。

    48 引用 • 206 回帖 • 347 关注
  • 星云链

    星云链是一个开源公链,业内简单的将其称为区块链上的谷歌。其实它不仅仅是区块链搜索引擎,一个公链的所有功能,它基本都有,比如你可以用它来开发部署你的去中心化的 APP,你可以在上面编写智能合约,发送交易等等。3 分钟快速接入星云链 (NAS) 测试网

    3 引用 • 16 回帖 • 2 关注
  • MySQL

    MySQL 是一个关系型数据库管理系统,由瑞典 MySQL AB 公司开发,目前属于 Oracle 公司。MySQL 是最流行的关系型数据库管理系统之一。

    677 引用 • 535 回帖
  • PHP

    PHP(Hypertext Preprocessor)是一种开源脚本语言。语法吸收了 C 语言、 Java 和 Perl 的特点,主要适用于 Web 开发领域,据说是世界上最好的编程语言。

    179 引用 • 407 回帖 • 489 关注