SQL Server 2005透视表运算符PIVOT应用示例

有用SQL写过交叉报表的，往往都比较头痛，还好现在SQL2005中提供了新的PIVOT操作符，可以很简单地写出交叉数据查询。正好前两天在研究 ORACLE最新的11G版本提供的新特性，发现ORACLE11G也同样推出这个新PIVOT，而且语法格式也几乎是一样的，呵，看来这些主流的数据库都同质化了，这对我们用户来说，也是一件好事，可以减少学习时间。晕，有点跑题了...
PIVOT的语法格式可以查询SQL2005的帮助文件，里面有详细说明，这里就不多说，以今天http://bbs.sunwy.com/thread-23185-1-1.html里提到的为例做一下介绍。
1、首先生成测试数据：

代码:

Create TABLE
  T1(Store varchar(20),
     PartNum varchar(10),
     QTY int)
GO
INSERT INTO T1
  SELECT  '成品仓','1001',5 UNION ALL
  SELECT  '成品仓','1002',5 UNION ALL
  SELECT  '成品仓','1003',5 UNION ALL
  SELECT  '成品仓','1004',5 UNION ALL
  SELECT  '原料仓','1001',5 UNION ALL
  SELECT  '原料仓','1002',5 UNION ALL
  SELECT  '原料仓','1003',5 UNION ALL
  SELECT  '销售部','1001',5 UNION ALL
  SELECT  '销售部','1003',5 UNION ALL
  SELECT  '销售部','1004',5
GO

2、开始查询

代码:


SELECT *
  FROM T1 PIVOT (
            SUM(QTY)
   FOR Store IN ([成品仓],[原料仓],[销售部])
    ) as pvt
  ORDER BY PartNum

生成结果

引用:

PartNum    成品仓         原料仓         销售部
---------- ----------- ----------- -----------
1001        5               5                  5
1002        5               5                  NULL
1003        5               5                  5
1004        5               NULL             5
(4 行受影响)

怎么样看到效果了吧，是不是很简单？
对PVIOT里的参数简单说明一下：
SUM(QTY)：数据项，对数量进行求各，当然也可选择其它聚合函数，如COUNT（QTY）等
FOR Store IN ([成品仓],[原料仓],[销售部])：指明显示在列区域的字段及相应的取值范围
AS pvt：指定PVT的表别名，当然也可指定为其它的表别名。

 

同样反透视也很简单

插入测试数据

select * into test from (
SELECT *
  FROM T1 PIVOT (
            SUM(QTY)
   FOR Store IN ([成品仓],[原料仓],[销售部])
    ) as pvt) as a

 

select * from T1
--select * from test
select store,partnum,profile as QTY
from test
unpivot(profile  for store in([成品仓],[原料仓],[销售部])) as n

这里就不往下深入了，有兴趣的可以查询SQL帮助文档或是在网上搜索一下，在此只是做抛砖引玉，给大家一个思路，建议去学习新的数据库平台上提供新的特性来减轻我们的工作量和提高工作效率。(From: http://bbs.sunwy.com/viewthread.php?tid=23253)