SQLServer使用 PIVOT 和 UNPIVOT行列转换

2024-06-15 10:28

本文主要是介绍SQLServer使用 PIVOT 和 UNPIVOT行列转换,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

在SQL Server中,PIVOT是一个用于将行数据转换为列数据的操作。它特别适用于将多个行中的值转换为多个列的情况,并在此过程中执行聚合操作。以下是关于SQL Server中PIVOT操作的详细解释和示例:

1、本文内容

  • 概述
  • 语法
  • 备注
  • 关键点
  • 简单 PIVOT 示例

适用于:

  • SQL Server
  • Azure SQL 数据库
  • Azure SQL 托管实例
  • Azure Synapse Analytics
  • Analytics Platform System (PDW)

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

PIVOT 的语法比一系列复杂的 SELECT…CASE 语句中所指定的语法更简单和更具可读性。
有关 PIVOT 语法的完整说明,请参阅 FROM (Transact-SQL)。
https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver16
https://learn.microsoft.com/en-us/sql/t-sql/queries/from-transact-sql?view=sql-server-ver16

2、PIVOT概述

  • 目的:将列值旋转为列名(即行转列),并在必要时对最终输出中所需的任何其余列值执行聚合。
  • 使用场景:当需要从多行数据中提取特定列的唯一值,并将这些值转换为列标题时。

3、语法

SELECT <non-pivoted column>,                                           -- [非透视的列],[first pivoted column] AS <column name>,                           -- [第一个透视的列] AS [列名称1][second pivoted column] AS <column name>,                          -- [第二个透视的列] AS [列名称2]         ...  [last pivoted column] AS <column name>                             -- [最后一个透视的列] AS [列名称N]
FROM  (<SELECT query that produces the data>)   AS <alias for the source query>  
PIVOT  
(  <aggregation function>(<column being aggregated>)                  -- [聚合函数]([要聚合的列]) 
FOR   
[<column that contains the values that will become column headers>]    -- [<包含要成为列标题的值的列>]IN ( [first pivoted column], [second pivoted column],  ... [last pivoted column])                                         -- [第一个透视的列],   [第二个透视的列],  ...  [最后一个透视的列]
) AS <alias for the pivot table>  
<optional ORDER BY clause>;

4、备注

UNPIVOT 子句中的列标识符需遵循目录排序规则。 对于 SQL 数据库,排序规则始终是 SQL_Latin1_General_CP1_CI_AS。 对于 SQL Server 部分包含的数据库,排序规则始终是 Latin1_General_100_CI_AS_KS_WS_SC。 如果将该列与与其他列合并,则需要 collate 子句 (COLLATE DATABASE_DEFAULT) 以避免冲突。

在 Microsoft Fabric 和 Azure Synapse Analytics 池中,如果 PIVOT 输出的非 pivot 列上存在 GROUP BY,则 PIVOT 运算符的查询将失败。 解决方法是从 GROUP BY 中删除非 pivot 列。 查询结果是相同的,因为此 GROUP BY 子句是重复的。

5、关键点

  • PIVOT必须列举值:在PIVOT操作中,必须明确列举出要转换为列标题的值。这些值将作为新表的字段名称。
  • 聚合函数:PIVOT操作中通常需要使用聚合函数(如SUM、AVG、MAX、MIN等)对数据进行聚合。虽然语法中没有明确显示GROUP BY子句,但PIVOT实际上是隐式地对数据进行分组和聚合的。
  • 处理空值:如果在原始表中某个分组没有对应的数据,那么PIVOT后的新表中该分组对应的列将以NULL值存在。
  • 与UNPIVOT的关系:PIVOT和UNPIVOT是相反的操作。UNPIVOT将列转换为列值,而PIVOT则将列值转换为列。

6、简单 PIVOT 示例

示例表信息,显示2024年每月的V-CUT和UV固化,背钻流程工步的过账面积

select * from t_PassOver_pivot
goPassOver_Month OutTechNo TechName                       OutQty_Area
-------------- --------- ------------------------------ ----------------------
2024-02        1803      V-CUT                          454.96
2024-03        1803      V-CUT                          1054.38
2024-04        1803      V-CUT                          1139
2024-01        1803      V-CUT                          891.28
2024-05        1803      V-CUT                          1248.33
2024-02        1610      UV固化                          2881.89
2024-01        1610      UV固化                          4281.75
2024-04        1610      UV固化                          4832.2
2024-03        1610      UV固化                          5430.31
2024-05        1610      UV固化                          4840.63
2024-01        1715      背钻                            1807.23
2024-05        1715      背钻                            1406.53

但是 1715 背钻 没有 2024-02,2024-03,2024-04 3月的过账面积。

以下代码显示相同的结果,该结果经过透视以使 PassOver_Month 过账月份值成为列标题。

SELECT OutTechNo,TechName,[2024-01] AS Month_202401,[2024-02] AS Month_202402,[2024-03] AS Month_202403,[2024-04] AS Month_202404,[2024-05] AS Month_202405FROM t_PassOver_pivot /*数据源*/
AS P
PIVOT 
(SUM(OutQty_Area/*行转列后 列的值*/) FOR p.PassOver_Month/*需要行转列的列*/ IN ([2024-01],[2024-02],[2024-03],[2024-04],[2024-05]/*列的值*/)
) AS T

在这里插入图片描述
提供个五列表示2024年前五个月份,因1715 背钻流程工步 没有 2024-02,2024-03,2024-04 3月的过账面积,即使结果为 NULL。

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

7、UNPIVOT

UNPIVOT 逆透视示例
与 PIVOT 执行的操作几乎相反,UNPIVOT将列转换为列值即多列转换为一列,而PIVOT则将列值转换为列即一列的多行数据转为多列。

select * from t_PassOver_unpivot

数据表t_PassOver_unpivot如下信息
在这里插入图片描述

SELECT PassOver_Month,OutTechNo,TechName,OutQty_Area  
FROM   (SELECT OutTechNo,TechName,Month_202401, Month_202402,Month_202403,Month_202404,Month_202405FROM t_PassOver_unpivot) p  
UNPIVOT  (OutQty_Area FOR PassOver_Month IN   (Month_202401, Month_202402,Month_202403,Month_202404,Month_202405)  
)AS unpvt;  
GO  

在这里插入图片描述
请注意,UNPIVOT 并不完全是 PIVOT 的逆操作。 PIVOT 执行聚合,并将多个可能的行合并为输出中的一行。 UNPIVOT 不重现原始表值表达式的结果,因为行已被合并。

另外,UNPIVOT 输入中的 NULL 值也在输出中消失了。 如果值消失,表明在执行 PIVOT 操作前,输入中可能就已存在原始 NULL 值。

这篇关于SQLServer使用 PIVOT 和 UNPIVOT行列转换的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!


原文地址:
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.chinasem.cn/article/1063189

相关文章

Mysql中的用户管理实践

《Mysql中的用户管理实践》:本文主要介绍Mysql中的用户管理实践,本文通过实例代码给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录13. 用户管理13.1 用户 13.1.1 用户信息 13.1.2 创建用户 13.1.3 删除用户 13.1.4 修改用户

使用Python实现网页表格转换为markdown

《使用Python实现网页表格转换为markdown》在日常工作中,我们经常需要从网页上复制表格数据,并将其转换成Markdown格式,本文将使用Python编写一个网页表格转Markdown工具,需... 在日常工作中,我们经常需要从网页上复制表格数据,并将其转换成Markdown格式,以便在文档、邮件或

Python使用pynput模拟实现键盘自动输入工具

《Python使用pynput模拟实现键盘自动输入工具》在日常办公和软件开发中,我们经常需要处理大量重复的文本输入工作,所以本文就来和大家介绍一款使用Python的PyQt5库结合pynput键盘控制... 目录概述:当自动化遇上可视化功能全景图核心功能矩阵技术栈深度效果展示使用教程四步操作指南核心代码解析

使用Python获取JS加载的数据的多种实现方法

《使用Python获取JS加载的数据的多种实现方法》在当今的互联网时代,网页数据的动态加载已经成为一种常见的技术手段,许多现代网站通过JavaScript(JS)动态加载内容,这使得传统的静态网页爬取... 目录引言一、动态 网页与js加载数据的原理二、python爬取JS加载数据的方法(一)分析网络请求1

MySQL查看表的最后一个ID的常见方法

《MySQL查看表的最后一个ID的常见方法》在使用MySQL数据库时,我们经常会遇到需要查看表中最后一个id值的场景,无论是为了调试、数据分析还是其他用途,了解如何快速获取最后一个id都是非常实用的技... 目录背景介绍方法一:使用MAX()函数示例代码解释适用场景方法二:按id降序排序并取第一条示例代码解

SpringCloud使用Nacos 配置中心实现配置自动刷新功能使用

《SpringCloud使用Nacos配置中心实现配置自动刷新功能使用》SpringCloud项目中使用Nacos作为配置中心可以方便开发及运维人员随时查看配置信息,及配置共享,并且Nacos支持配... 目录前言一、Nacos中集中配置方式?二、使用步骤1.使用$Value 注解2.使用@Configur

Mac备忘录怎么导出/备份和云同步? Mac备忘录使用技巧

《Mac备忘录怎么导出/备份和云同步?Mac备忘录使用技巧》备忘录作为iOS里简单而又不可或缺的一个系统应用,上手容易,可以满足我们日常生活中各种记录的需求,今天我们就来看看Mac备忘录的导出、... 「备忘录」是 MAC 上的一款常用应用,它可以帮助我们捕捉灵感、记录待办事项或保存重要信息。为了便于在不同

Python+PyQt5实现MySQL数据库备份神器

《Python+PyQt5实现MySQL数据库备份神器》在数据库管理工作中,定期备份是确保数据安全的重要措施,本文将介绍如何使用Python+PyQt5开发一个高颜值,多功能的MySQL数据库备份工具... 目录概述功能特性核心功能矩阵特色功能界面展示主界面设计动态效果演示使用教程环境准备操作流程代码深度解

如何清理MySQL中的binlog问题

《如何清理MySQL中的binlog问题》:本文主要介绍清理MySQL中的binlog问题,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目http://www.chinasem.cn录清理mysql中的binlog1.查看binlog过期时间2. 修改binlog过期

MySQL基本查询示例总结

《MySQL基本查询示例总结》:本文主要介绍MySQL基本查询示例总结,本文通过实例代码给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录Create插入替换Retrieve(读取)select(确定列)where条件(确定行)null查询order by语句li