Transact-SQL 使用 PIVOT 和 UNPIVOT 实现行与列之间的转化

晨曦之光 发布于 2012/06/05 16:54
阅读 124
收藏 0

I:使用 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
    Exss Edition with Advanced Services on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)
然后我们选择一个数据库然后右键-属性 选择[选项]得到下图的信息
image 
在确认数据库的版本和兼容级别符合1,2点的要求后你才可以接着继续往下学习

II:使用 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 ]

image

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

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

A.一个称为源表(SourceTable)

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

< class="brush:sql;gutter:false;">语法 SELECT <未透视的列>, [第一个透视列] AS <列别名>, [第二个透视列] AS <列别名>, ... [最后一个透视列] AS <列别名> FROM ( <SELECT查询> ) AS <源表> PIVOT ( <聚合函数>(<列>) FOR [<需要转换为行的列>] IN ( [第一个透视列], [第二个透视列], ... [最后一个透视列] ) ) AS <数据透视表> <可选的ORDER BY子句>;

以上的PIVOT子句内的第1…n个透视列的值均为 需要转换为行的列 的常量值,需要用[]括起,支持GUID,字符串及各种数字!
具体Technet地址为: http://technet.microsoft.com/zh-cn/library/ms177410(SQL.100).aspx

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

-- 使用 PIVOT 查询班级内的男女学生人数及总人数
SELECT
[ 所属班级 ] AS [ 班级 ] ,
[ ] AS [ 男生人数 ] ,
[ ] AS [ 女生人数 ] ,
[ ] + [ ] AS [ 总人数 ]
FROM (
SELECT [ 学生编号 ] , [ 所属班级 ] , [ 性别 ] FROM #Student
)
AS [ SourceTable ]
PIVOT (
COUNT ( [ 学生编号 ] )
FOR [ 性别 ] IN (
[ ] , [ ]
)
)
AS [ PivotTable ]
ORDER BY [ 总人数 ] DESC

image

III:使用 UNPIVOT 实现的功能其实与PIVOT恰恰相反

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

< class="brush:sql;gutter:false;">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 ]

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

-- 结果
SELECT
[ 班级 ] , [ 总人数 ]
FROM (
SELECT
[ 初一 1班 ] , [ 初一 2班 ] ,
[ 初二 1班 ] ,
[ 初三 1班 ] , [ 初三 2班 ]
FROM
#PivotTable
)
AS [ s ]
UNPIVOT (
[ 总人数 ]
FOR [ 班级 ] IN (
[ 初一 1班 ] , [ 初一 2班 ] ,
[ 初二 1班 ] ,
[ 初三 1班 ] , [ 初三 2班 ]
)
)
AS [ un_p ]

image
在这里也写个比较高级的希望各位DBA莫笑!
觉得好的请[推荐]一下下,本人时间有限,未能逐一回复,请见晾!首先谢谢各位看过本文的朋友!

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 ]

image

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

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

image
本文已结束!!


原文链接:http://www.cnblogs.com/highend/archive/2011/07/15/how_to_using_pivot_and_unpivot.html
加载中
返回顶部
顶部