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

SQL Server 2005 行列转换

有用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的表别名，当然也可指定为其它的表别名。

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

--测试PIVOT
IF OBJECT_ID(N'T_PIVOT',N'U') IS NOT NULL
    DROP TABLE T_PIVOT
GO
CREATE TABLE T_PIVOT(
id INT,
name VARCHAR(20),
[quarter] INT,
amount INT
)
GO
INSERT INTO T_PIVOT
SELECT id = 1,name = 'a',[quarter] = 1,amount = 1000
UNION ALL
SELECT id = 1,name = 'a',[quarter] = 2,amount = 2000
UNION ALL
SELECT id = 1,name = 'a',[quarter] = 2,amount = 2000
UNION ALL
SELECT id = 1,name = 'a',[quarter] = 2,amount = 2000
UNION ALL
SELECT id = 1,name = 'a',[quarter] = 3,amount = 3000
UNION ALL
SELECT id = 1,name = 'a',[quarter] = 4,amount = 3500
UNION ALL
SELECT id = 2,name = 'b',[quarter] = 1,amount = 2000
UNION ALL
SELECT id = 2,name = 'b',[quarter] = 2,amount = 2500
UNION ALL
SELECT id = 2,name = 'b',[quarter] = 3,amount = 4000
UNION ALL
SELECT id = 2,name = 'b',[quarter] = 4,amount = 4500
GO
SELECT * FROM T_PIVOT
GO
SELECT id,name,
                 [1] AS 'Q1',
                 [2] AS 'Q2',
                 [3] AS 'Q3',
                 [4] AS 'Q4'
FROM T_PIVOT
PIVOT(
         SUM(amount)
         FOR [quarter] IN([1],[2],[3],[4])
) AS PP
GO
--测试UNPIVOT
IF OBJECT_ID(N'T_UNPIVOT',N'U') IS NOT NULL
     DROP TABLE T_UNPIVOT
GO
CREATE TABLE T_UNPIVOT(
id INT,
name VARCHAR(20),
Q1 INT,
Q2 INT,
Q3 INT,
Q4 INT
)
GO
INSERT     INTO     T_UNPIVOT
SELECT id,name,
                [1] AS 'Q1',
                [2] AS 'Q2',
                [3] AS 'Q3',
                 [4] AS 'Q4'
FROM     T_PIVOT
PIVOT(
         COUNT(amount)
         FOR [quarter] IN([1],[2],[3],[4])
) AS     PP
GO
SELECT * FROM T_UNPIVOT
GO
SELECT     id,name,amount,[quarter]
FROM     T_UNPIVOT
UNPIVOT(
         amount FOR [quarter] IN ([Q1],[Q2],[Q3],[Q4])
) AS     PP