一、使用PIVOT和UNPIVOT命令的SQL Server版本要求
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)
然后我们选择一个数据库然后右键-属性 选择[选项]得到下图的信息。
在确认数据库的版本和兼容级别符合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班';
以下是查询的结果:
学生编号
|
姓名
|
性别
|
所属班级
|
1
|
李妹妹
|
女
|
初一 1班
|
2
|
泰强
|
男
|
初一 1班
|
3
|
泰映
|
男
|
初一 1班
|
4
|
何谢
|
男
|
初一 1班
|
5
|
李春
|
男
|
初二 1班
|
6
|
吴歌
|
男
|
初二 1班
|
7
|
林纯
|
男
|
初二 1班
|
8
|
徐叶
|
女
|
初二 1班
|
9
|
龙门
|
男
|
初三 1班
|
10
|
小红
|
女
|
初三 1班
|
11
|
小李
|
男
|
初三 1班
|
12
|
小黄
|
女
|
初三 2班
|
13
|
旺财
|
男
|
初三 2班
|
14
|
强强
|
男
|
初二 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]
在结果表中我们看到了对于不存在的班级初一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
三、使用UNPIVOT 实现的功能其实与PIVOT恰恰相反
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]
将多个列合并到单个列的转换的语句!!!
--结果
- SELECT
-
- [班级], [总人数]
-
- FROM (
-
- SELECT
-
- [初一 1班], [初一 2班],
-
- [初二 1班],
-
- [初三 1班], [初三 2班]
-
- FROM
-
- #PivotTable
-
- ) AS [s]
-
- UNPIVOT (
-
- [总人数]
-
- FOR [班级] IN (
-
- [初一 1班], [初一 2班],
-
- [初二 1班],
-
- [初三 1班], [初三 2班]
-
- )
-
- ) AS [un_p]
执行下面代码:
- 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]
或者将性别和人数合并到一个列当中:
- SELECT
-
- [班级],
-
- [性别] + ': ' + CAST([男生或女生人数] AS NVARCHAR(1)) AS [男生或女生人数],
-
- [总人数]
-
- FROM (
-
- SELECT [班级], [男生人数], [女生人数], [总人数] FROM #PivotTable2
-
- ) AS [s]
-
- UNPIVOT (
-
- [男生或女生人数]
-
- FOR [性别] IN (
-
- [男生人数],
-
- [女生人数]
-
- )
-
- ) AS [un_p]
关于PIVOT和UNPIVOT命令的使用就介绍到这里,如果想了解更多SQL的知识可以去看看这里的文章:http://database.51cto.com/sqlserver/,绝对不会让您失望的哦!