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帮助文档或是在网上搜索一下,在此只是做抛砖引玉,给大家一个思路,建议去学习新的数据库平台上提供新的特性来减轻我们的工作量和提高工作效率。(From: http://bbs.sunwy.com/viewthread.php?tid=23253)
 
 
--测试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
 
 
posted @   emanlee  阅读(606)  评论(0编辑  收藏  举报
编辑推荐:
· 开发者必知的日志记录最佳实践
· SQL Server 2025 AI相关能力初探
· Linux系列:如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列(二):开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
阅读排行:
· 开源Multi-agent AI智能体框架aevatar.ai,欢迎大家贡献代码
· Manus重磅发布:全球首款通用AI代理技术深度解析与实战指南
· 被坑几百块钱后,我竟然真的恢复了删除的微信聊天记录!
· 没有Manus邀请码?试试免邀请码的MGX或者开源的OpenManus吧
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
历史上的今天:
2007-10-21 C语言程序设计 搜集C/C++编译器
2007-10-21 2008秋-计算机软件基础-直接插入排序
2007-10-21 2008秋-计算机软件基础-第四章习题参考答案 P131 ,习题6之二
2007-10-21 2008秋-计算机软件基础-快速排序
2007-10-21 C语言程序设计 参考书籍
点击右上角即可分享
微信分享提示