Sql Server2005 Transact-SQL 新兵器学习总结之-PIVOT和UNPIVOT运算符-白红宇

1.简介

PIVOT通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式，并在必要时对最终输出中所需的任何其余列值执行聚合。

UNPIVOT与PIVOT执行相反的操作，将表值表达式的列转换为列值。

通俗简单的说：PIVOT就是行转列，UNPIVOT就是列传行

2.例题

复制代码

--建立销售表

CREATE TABLE Sell

(

[Year] INT,

[Quarter] NVARCHAR(10),

Quantity INT

)

--插入测试数据

INSERT INTO Sell

VALUES ( 2006, 'Q1', 20 )

INSERT INTO Sell

VALUES ( 2006, 'Q2', 15 )

INSERT INTO Sell

VALUES ( 2006, 'Q2', 4 )

INSERT INTO Sell

VALUES ( 2006, 'Q3', 12 )

INSERT INTO Sell

VALUES ( 2006, 'Q4', 18 )

INSERT INTO Sell

VALUES ( 2007, 'Q1', 10 )

INSERT INTO Sell

VALUES ( 2007, 'Q2', 10 )

INSERT INTO Sell

VALUES ( 2008, 'Q1', 8 )

INSERT INTO Sell

VALUES ( 2008, 'Q2', 7 )

INSERT INTO Sell

VALUES ( 2008, 'Q3', 5 )

INSERT INTO Sell

VALUES ( 2008, 'Q3', 10 )

INSERT INTO Sell

VALUES ( 2008, 'Q4', 9 )

--得到每年每季度的销售总数

SELECT *

FROM Sell PIVOT ( SUM(Quantity) FOR [Quarter] IN ( Q1, Q2, Q3, Q4 ) ) AS P

--查询得如下结果

--注意：

--如果子组为空，SQL Server生成空值。如果聚合函数是COUNT，且子组为空，则返回零。

Year Q1 Q2 Q3 Q4

2006 20 19 12 18

2007 10 10 NULL NULL

2008 8 7 15 9

其实PIVOT在sql2000中可以用SELECT...CASE语句来实现，下面是sql2000的代码：

复制代码

--sql 2000 静态版本

SELECT [year],

SUM(CASE WHEN [Quarter] = 'Q1' THEN Quantity

END) AS Q1,

SUM(CASE WHEN [Quarter] = 'Q2' THEN Quantity

END) AS Q2,

SUM(CASE WHEN [Quarter] = 'Q3' THEN Quantity

END) AS Q3,

SUM(CASE WHEN [Quarter] = 'Q4' THEN Quantity

END) AS Q4

FROM sell

GROUP BY [year]

复制代码

--sql 2000 动态版本

DECLARE @sql NVARCHAR(2000)

SELECT @sql = 'select [year] '

SELECT @sql = @sql + ',sum(case when [Quarter] =''' + [Quarter]

+ ''' then Quantity end) as ' + [Quarter]

FROM sell

GROUP BY [Quarter]

ORDER BY [Quarter] ASC

select @sql = @sql + ' from sell group by [year] '

execute ( @sql )

复制代码

UNPIVOT将与PIVOT执行几乎完全相反的操作，将列转换为行。

复制代码

--创建测试表

CREATE TABLE TestUNPIVOT

(

ID INT,

A1 NVARCHAR(10),

A2 NVARCHAR(10),

A3 NVARCHAR(10)

)

--插入测试数据

INSERT INTO TestUNPIVOT

VALUES ( 1, 'q1', 'q2', 'q3' )

INSERT INTO TestUNPIVOT

VALUES ( 2, 'q1', 'p1', 'm1' )

INSERT INTO TestUNPIVOT

VALUES ( 3, 't1', 'p1', 'm1' )

复制代码

--UNPIVOT

SELECT ID,

[Value]

FROM ( SELECT ID,

A1,

A2,

FROM TestUNPIVOT

) p UNPIVOT ( [Value] FOR A IN ( A1, A2, A3 ) )AS u

ORDER BY id ASC,

a ASC

复制代码

--查询得如下结果

ID A Value

1 A1 q1

1 A2 q2

1 A3 q3

2 A1 q1

2 A2 p1

2 A3 m1

3 A1 t1

3 A2 p1

3 A3 m1

复制代码

--UNPIVOT 的sql 2000 实现语句：

SELECT id,

'a1' AS [A],

a1 AS [Value]

FROM TestUNPIVOT

UNION ALL

SELECT id,

'a2',

FROM TestUNPIVOT

UNION ALL

SELECT id,

'a3',

FROM TestUNPIVOT

ORDER BY id ASC, a ASC

复制代码

3.总结

个人感觉PIVOT运算符相比SELECT...CASE语句就是代码精简了一些,似乎PIVOT可读性好像不太好！

至少我看起来PIVOT语法有点怪怪，也许是还习惯吧！我个人还是喜欢用SELECT...CASE语句.

希望微软能提供PIVOT运算符的动态版本，这样动态生成列时，不用那么费事的累加字符串

4.推荐

本系列文章(新兵器学习总结)推荐

复制代码

我的系列文章

A.Sql Server2005 Transact-SQL 新兵器学习

B.MCAD学习

C.代码阅读总结

D.ASP.NET状态管理

E.DB(数据库)

F.WAP

G.WinForm

H.Flex

我的好文推荐

FlexAir开源版-全球免费多人视频聊天室,免费网络远程多人视频会议系统((Flex,Fms3联合开发))<视频聊天,会议开发实例8>

Sql Server2005 Transact-SQL 新兵器学习总结之-总结

MS SQL数据库备份和恢复存储过程(加强版本)

sql server中分布式查询随笔(链接服务器(sp_addlinkedserver)和远程登录映射(sp_addlinkedsrvlogin)使用小总结)

ASP.NET2.0国际化/本地化应用程序的实现总结(多语言,多文化页面的实现)

WAP开发资料站(最新更新)

自定义格式字符串随笔 (IFormattable,IFormatProvider,ICustomFormatter三接口的实现)

Mcad学习笔记之异步编程(AsyncCallback 委托,IAsyncResult接口,BeginInvoke方法,EndInvoke方法的使用小总结)

Mcad学习笔记之通过反射调用類的方法,屬性,字段,索引器(2種方法)

Mcad学习笔记之序列化(2进制和Soap序列化)

Mcad学习笔记之委托再理解(delegate的构造器,BeginInvoke,EndInvoke,Invoke4个方法的探讨)

ASP.NET状态管理之一（概括篇）

Flex,Fms学习笔记

本文转自aierong博客园博客，原文链接http://www.cnblogs.com/aierong/archive/2008/09/01/1279463.html如需转载请自行联系原作者