首页 > 数据库 >MSSQL PIVOT和UNPIVOT使用说明

MSSQL PIVOT和UNPIVOT使用说明

时间：2023-03-23 17:00:39浏览次数：43  
标签：班级 初三 UNPIVOT 初一 PIVOT 人数 MSSQL SELECT 

一、版本要求

1.数据库的最低版本要求为SQL Server 2005 或更高。

2.必须将数据库的兼容级别设置为90 或更高。

3.查看我的数据库版本及兼容级别。

如果不知道怎么看数据库版本或兼容级别的话可以在SQL Server Management Studio新建一个查询窗口输入：print @@version，运行之后在我的本机上得到：

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86)

Apr  2 2010 15:53:02

Copyright (c) Microsoft Corporation

Express Edition with Advanced Services on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)

然后我们选择一个数据库然后右键-属性 选择[选项]得到下图的信息。

T-SQL行列相互转换命令：PIVOT和UNPIVOT使用详解 

在确认数据库的版本和兼容级别符合1,2点的要求后你才可以接着继续往下学习。

二、PIVOT 实现行转列

1.在这里我们先构建一个测试数据表(这里使用的是临时表，以方便我们在退出会话的时候自动删除表及其数据)

首先我们先设计一个表架构为#Student { 学生编号[PK],  姓名, 性别, 所属班级 }的表,然后编写如下T-SQL

--创建临时表(仅演示,表结构的不合理还请包涵) 

CREATE TABLE #Student ([学生编号] INT IDENTITY(1, 1) PRIMARY KEY, [姓名] NVARCHAR(20), [性别] NVARCHAR(1), [所属班级] NVARCHAR(20))

--给临时表插入数据

复制代码 
INSERT INTO #Student ([姓名], [性别], [所属班级]) 
SELECT '李妹妹', '女', '初一 1班' UNION ALL 
SELECT '泰强', '男', '初一 1班' UNION ALL 
SELECT '泰映', '男', '初一 1班' UNION ALL 
SELECT '何谢', '男', '初一 1班' UNION ALL 
SELECT '李春', '男', '初二 1班' UNION ALL 
SELECT '吴歌', '男', '初二 1班' UNION ALL 
SELECT '林纯', '男', '初二 1班' UNION ALL 
SELECT '徐叶', '女', '初二 1班' UNION ALL 
SELECT '龙门', '男', '初三 1班' UNION ALL 
SELECT '小红', '女', '初三 1班' UNION ALL 
SELECT '小李', '男', '初三 1班' UNION ALL 
SELECT '小黄', '女', '初三 2班' UNION ALL 
SELECT '旺财', '男', '初三 2班' UNION ALL 
SELECT '强强', '男', '初二 1班';
复制代码

以下是查询的结果： 


2.查询各班级的总人数

复制代码 
SELECT [所属班级] AS [班级], COUNT(1) AS [人数] FROM #Student GROUP BY [所属班级] ORDER BY [人数] DESC

班级       人数
-------- -----------
初二 1班    5
初一 1班    4
初三 1班    3
初三 2班    2
复制代码

好了，在这里我希望把上面的表{ 班级, 人数 } 由 班级[行] 的显示转换为 班级[列] 的显示格式!

在此你会看到第一个PIVOT示例。是否很期待??

 

3.编写第一个PIVOT示例

复制代码 
SELECT '班级总人数:' AS [总人数], [初一 1班], [初一 2班], [初二 1班], [初三 1班], [初三 2班] 
FROM (
    SELECT [所属班级] AS [班级], [姓名] FROM #Student) AS [SourceTable] 
    PIVOT (COUNT([姓名]) FOR [班级] IN ([初一 1班], [初一 2班], [初二 1班], [初三 1班], [初三 2班]) ) AS [PivotTable]

总人数         初一 1班       初一 2班       初二 1班       初三 1班       初三 2班
----------- ----------- ----------- ----------- ----------- -----------
班级总人数:      4           0           5           3           2
复制代码

在结果表中我们看到了对于不存在的班级初一2班它的总人数为0，这符合我们预期的结果!

 

解释：使用POVIT首先你需要在FROM子句内定义2个表：

A.一个称为源表(SourceTable)。

B.另一个称为数据透视表(PivotTable)。

语法：

复制代码 
SELECT <未透视的列>, 
[第一个透视列] AS <列别名>, 
[第二个透视列] AS <列别名>, 
... 
[最后一个透视列] AS <列别名> 
FROM (<SELECT查询>) AS <源表> PIVOT (<聚合函数>(<转换后列的值>) FOR [<需要转换为行的列>] IN ([第一个透视列], [第二个透视列],... [最后一个透视列])) AS <数据透视表> 
<可选的ORDER BY子句>;
复制代码

以上的PIVOT子句内的第1…n个透视列的值均为需要转换为行的列的常量值,需要用[]括起，支持GUID，字符串及各种数字!

 

4.下面演示一个较为高级的行转列的应用示例

--使用PIVOT查询班级内的男女学生人数及总人数

复制代码 
SELECT [所属班级] AS [班级], [男] AS [男生人数], [女] AS [女生人数], [男] + [女] AS [总人数] 
FROM (
    SELECT [姓名], [所属班级], [性别] FROM #Student) AS [SourceTable] 
    PIVOT (COUNT([姓名]) FOR [性别] IN ([男], [女])) AS [PivotTable] 
    ORDER BY [总人数] DESC

班级       男生人数        女生人数        总人数
-------- ----------- ----------- -----------
初二 1班    4           1           5
初一 1班    3           1           4
初三 1班    2           1           3
初三 2班    1           1           2
复制代码

 

三、UNPIVOT 实现列转行

1.语法同PIVOT但是UNPIVOT的子句没有聚合函数

复制代码 
SELECT  
<未逆透视的列>,  
[合并后的列] AS <列别名>,  
[行值的列名] AS <列别名> 
FROM (  
<SELECT查询> 
) AS <源表> 
UNPIVOT (  
<行值的列名> 
FOR <将原来多个列合并到单个列的列名> IN (  
[第一个合并列], [第二个合并列],  
...  
[最后一个合并列]  
)  
) AS <数据逆透视表> 
<可选的ORDER BY子句>;
复制代码

 

2.看上面的语法感觉很浮云，不怕，这里带例子(继续使用II中用到的PIVOT表)

 --源表

复制代码 
SELECT  '班级总人数:' AS [总人数],  [初一 1班], [初一 2班],  [初二 1班],  [初三 1班], [初三 2班]  
INTO #PivotTable --为了使表达意图更清晰,我把PIVOT处理后的表放到一个临时表当中  
FROM (
    SELECT  
    [所属班级] AS [班级],  
    [学生编号]  
    FROM #Student  
    ) AS [SourceTable]  
PIVOT (COUNT([学生编号]) FOR [班级] IN ([初一 1班], [初一 2班], [初二 1班], [初三 1班], [初三 2班] ) ) AS [PivotTable]
复制代码

T-SQL行列相互转换命令：PIVOT和UNPIVOT使用详解

将多个列合并到单个列的转换的语句!!!

--结果

SELECT [班级], [总人数]
FROM (
SELECT [初一 1班], [初一 2班],[初二 1班], [初三 1班], [初三 2班]
FROM #PivotTable ) AS [s]
UNPIVOT ([总人数] for [班级] in ([初一 1班],[初一 2班],[初二 1班],[初三 1班], [初三 2班])) AS [un_p]

T-SQL行列相互转换命令：PIVOT和UNPIVOT使用详解 

 

执行下面代码：

复制代码


SELECT [所属班级] AS [班级],[男] AS [男生人数],[女] AS [女生人数],[男] + [女] AS [总人数]
INTO #PivotTable2 --放到临时表方便查询
FROM (
SELECT [学生编号], [所属班级], [性别] FROM #Student) AS [SourceTable]
PIVOT (COUNT([学生编号]) FOR [性别] IN ([男], [女])) AS [PivotTable]
ORDER BY [总人数] DESC 



SELECT [班级],[男生或女生人数],[性别],[总人数]
FROM (  
    SELECT [班级], [男生人数], [女生人数], [总人数] FROM #PivotTable2) AS [s]  
    UNPIVOT ([男生或女生人数]FOR [性别] IN ([男生人数],[女生人数])) AS [un_p]


 

复制代码


T-SQL行列相互转换命令：PIVOTH和UNPIVOT使用详解


 


或者将性别和人数合并到一个列当中：



SELECT [班级],[性别] + ': ' + CAST([男生或女生人数] AS NVARCHAR(1)) AS [男生或女生人数],[总人数]
FROM (SELECT [班级], [男生人数], [女生人数], [总人数] FROM #PivotTable2) AS [s]  
UNPIVOT ([男生或女生人数] FOR [性别] IN ([男生人数], [女生人数])) AS [un_p]



T-SQL行列相互转换命令：PIVOT和UNPIVOT使用详解


 




 


四、UNPIVOT


复制代码

declare @t table(name varchar(10),age int,salary decimal(8,2))
insert @t select 'Tom',24,5432.1
insert @t select 'John',26,5678.9
select * from @t
/*
name|age|salary |
----|---|-------|
Tom | 24|5432.10|
John| 26|5678.90|
*/

select [name],[type],[value] from @t 
unpivot(value for type in (age,salary)) b
--报错：SQL 错误 [8167] [S0001]: 列 "salary" 的类型与 UNPIVOT 列表中指定的其他列的类型冲突。
--分析：转换后的value列数据类型或大小不同，则会出错
--方法：将其强转一下
select [name],[type],[value] from 
(select name,age=cast(age as decimal(8,2)),salary from @t) a 
unpivot(value for type in (age,salary)) b
/*
name|type  |value  |
----|------|-------|
Tom |age   |  24.00|
Tom |salary|5432.10|
John|age   |  26.00|
John|salary|5678.90|
*/



参考：https://www.cnblogs.com/zhaoshujie/p/9594693.html，对其中的语法错误进行修改，红色标出。




标签：班级,初三,UNPIVOT,初一,PIVOT,人数,MSSQL,SELECT	

From： https://www.cnblogs.com/Jamesblog/p/17248087.html
      

      




      

      

        
相关文章

        

          
    
 
  • MSSQLSERVER 存储过程debug调试
    每当我们遇到相对稍复杂的业务的时候，都会考虑写在存储过程中，这样相当于一个黑匣，方便管理。 但是如果写的行数太多，如果碰到了问题，凭经验，很难发现问题，那就要用到debug调试......
     
  • 界面控件DevExtreme的Pivot Grid组件——轻松实现多维数据分析
    DevExtreme拥有高性能的HTML5/JavaScript小部件集合，使您可以利用现代Web开发堆栈（包括React，Angular，ASP.NETCore，jQuery，Knockout等）构建交互式的Web应用程序，该套件附带功能......
     
  • docker run mssql
    拉取镜像dockerpullmcr.microsoft.com/mssql/server:2019-latest创建挂载目录mkdir/data/mssql为挂载目录设置写入权限chmod-R777/data/mssql运行容......
     
  • #Python 利用pivot_table,数据透视表进行数据分析
     前面我们分享了，利用python进行数据合并和连接，但是工作中，我们往往需要对数据进一步的聚合或者运算，以求最后的数据结果。今天我们就来学习一下利用pandas模块，对数据集进......
     
  • MSSQL中开启CDC导致事务日志空间被占满事件记录
    问题描述SQLServer中开启CDC之后，在某些情况下会导致事务日志空间被占满的现象为：在执行增删改语句（产生事务日志）的过程中提示，Thetransactionlogfordatabase'***'isfu......
     
  • MSSQL结合RBCD提权
    MSSQL结合RBCD提权原理这里使用中继的方式给他中继到ldap中去添加msDS-AllowedToActOnBehalfOfOtherIdentity属性。默认域控的ms-DS-MachineAccountQuota属性设置允许所......
     
  • SQL Server 2008 R2 使用 PIVOT 错误
    SQLServer2008R2使用PIVOT错误！'PIVOT'附近有语法错误。您可能需要将当前数据库的兼容级别设置为更高的值，以启用此功能。有关ALTERDATABASE的SETCOMPATIBILITY_L......
     
  • Office - Excel - 数据透视表 Pivot Table
     一、透视表数据源可以通过Excel直接去链接数据库（需要公司本身支持才行）也可以把需要分析的数据，从数据库中直接拷贝出来--这里演示的是这一种： 将结果集，放在一个ta......
     
  • SqlServer 多服务器管理（MSSQL分布式作业管理）
    范围的多个实例的自动化管理SQLServer称为多服务器管理。使用多服务器管理可以执行下列操作：管理两台或多台服务器。在企业服务器之间安排数据仓库的信息流。若要利用......
     
  • MSSQL攻击面探索
    xp_cmdshell开启id=1'EXECsp_configure'showadvancedoptions',1;RECONFIGURE;execSP_CONFIGURE'xp_cmdshell',1;RECONFIGURE;--+命令执行id=1'execm......
     
    •            
          

        

      

    

    

      
赞助商

      




      

      
阅读排行

      

        
      

    

  





  
 
    
网站主页关于我们联系我们网站地图 

    
本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。 

    
Copyright © 2020-2023  IPS99  版权所有 IPS99