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

相关文章

Mybatis嵌套子查询动态SQL编写实践

《Mybatis嵌套子查询动态SQL编写实践》:本文主要介绍Mybatis嵌套子查询动态SQL编写方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录前言一、实体类1、主类2、子类二、Mapper三、XML四、详解总结前言MyBATis的xml文件编写动态SQL

Python使用FFmpeg实现高效音频格式转换工具

《Python使用FFmpeg实现高效音频格式转换工具》在数字音频处理领域,音频格式转换是一项基础但至关重要的功能,本文主要为大家介绍了Python如何使用FFmpeg实现强大功能的图形化音频转换工具... 目录概述功能详解软件效果展示主界面布局转换过程截图完成提示开发步骤详解1. 环境准备2. 项目功能结

SpringBoot使用ffmpeg实现视频压缩

《SpringBoot使用ffmpeg实现视频压缩》FFmpeg是一个开源的跨平台多媒体处理工具集,用于录制,转换,编辑和流式传输音频和视频,本文将使用ffmpeg实现视频压缩功能,有需要的可以参考... 目录核心功能1.格式转换2.编解码3.音视频处理4.流媒体支持5.滤镜(Filter)安装配置linu

Redis中的Lettuce使用详解

《Redis中的Lettuce使用详解》Lettuce是一个高级的、线程安全的Redis客户端,用于与Redis数据库交互,Lettuce是一个功能强大、使用方便的Redis客户端,适用于各种规模的J... 目录简介特点连接池连接池特点连接池管理连接池优势连接池配置参数监控常用监控工具通过JMX监控通过Pr

解决mysql插入数据锁等待超时报错:Lock wait timeout exceeded;try restarting transaction

《解决mysql插入数据锁等待超时报错:Lockwaittimeoutexceeded;tryrestartingtransaction》:本文主要介绍解决mysql插入数据锁等待超时报... 目录报错信息解决办法1、数据库中执行如下sql2、再到 INNODB_TRX 事务表中查看总结报错信息Lock

MySQL启动报错:InnoDB表空间丢失问题及解决方法

《MySQL启动报错:InnoDB表空间丢失问题及解决方法》在启动MySQL时,遇到了InnoDB:Tablespace5975wasnotfound,该错误表明MySQL在启动过程中无法找到指定的s... 目录mysql 启动报错:InnoDB 表空间丢失问题及解决方法错误分析解决方案1. 启用 inno

apache的commons-pool2原理与使用实践记录

《apache的commons-pool2原理与使用实践记录》ApacheCommonsPool2是一个高效的对象池化框架,通过复用昂贵资源(如数据库连接、线程、网络连接)优化系统性能,这篇文章主... 目录一、核心原理与组件二、使用步骤详解(以数据库连接池为例)三、高级配置与优化四、典型应用场景五、注意事

MySQL 安装配置超完整教程

《MySQL安装配置超完整教程》MySQL是一款广泛使用的开源关系型数据库管理系统(RDBMS),由瑞典MySQLAB公司开发,目前属于Oracle公司旗下产品,:本文主要介绍MySQL安装配置... 目录一、mysql 简介二、下载 MySQL三、安装 MySQL四、配置环境变量五、配置 MySQL5.1

使用Python实现Windows系统垃圾清理

《使用Python实现Windows系统垃圾清理》Windows自带的磁盘清理工具功能有限,无法深度清理各类垃圾文件,所以本文为大家介绍了如何使用Python+PyQt5开发一个Windows系统垃圾... 目录一、开发背景与工具概述1.1 为什么需要专业清理工具1.2 工具设计理念二、工具核心功能解析2.

MySQL 添加索引5种方式示例详解(实用sql代码)

《MySQL添加索引5种方式示例详解(实用sql代码)》在MySQL数据库中添加索引可以帮助提高查询性能,尤其是在数据量大的表中,下面给大家分享MySQL添加索引5种方式示例详解(实用sql代码),... 在mysql数据库中添加索引可以帮助提高查询性能,尤其是在数据量大的表中。索引可以在创建表时定义,也可