SQL SERVER透视转换(行旋转列)
来源:互联网 发布:超级优化全本下载 编辑:程序博客网 时间:2024/05/11 01:51
所谓的透视转化通俗点就是吧数据库的行转成列的一种处理方式,透视转换是一种常用的技巧,在生活中人们更喜欢看经过透视转化的数据表,这种表更加直观和简洁,下面将介绍两种处理方式。
样例数据:
USE tempdb;
IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL DROP TABLE dbo.Orders;
GO
CREATE TABLE dbo.Orders
(
orderid INT NOT NULL,
orderdate DATE NOT NULL, -- prior to SQL Server 2008 use DATETIME
empid INT NOT NULL,
custid VARCHAR(5) NOT NULL,
qty INT NOT NULL,
CONSTRAINT PK_Orders PRIMARY KEY(orderid)
);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES
(30001, '20070802', 3, 'A', 10),
(10001, '20071224', 2, 'A', 12),
(10005, '20071224', 1, 'B', 20),
(40001, '20080109', 2, 'A', 40),
(10006, '20080118', 1, 'C', 14),
(20001, '20080212', 2, 'B', 12),
(40005, '20090212', 3, 'A', 10),
(20002, '20090216', 1, 'C', 20),
(30003, '20090418', 2, 'B', 15),
(30004, '20070418', 3, 'C', 22),
(30007, '20090907', 3, 'D', 30);
本篇博客,来自个人对MicroSoft SQL Server 2008技术内幕:T-SQL语言基础第七章总结,有有兴趣的朋友可以去原文;
在进一步解释透光转化是什么的时候,先考虑一个需求:生成一个报表,包含每个雇员和客户组合之间总订货量,下面的查询就可以解决这个需求了。
SELECT empid, custid, SUM(qty) AS sumqty
FROM dbo.Orders
GROUP BY empid, custid;
查询结果:
效果如下:
透视转换将要涉及三个逻辑处理阶段:
1.分组阶段:处理相关的分组或者行元素
i.这阶段判断以什么分组(empid),确定什么为行(empid)
2.扩展阶段:处理相关的扩展或列元素
i.判断以什么作为列(custid的列值[A,B,C,D])
3.聚合阶段:处理相关的聚合元素和聚合函数
i.要聚合那个列,用什么聚合函数
下面介绍第一种处理方式:
1.使用标准SQL进行透光转换
从透光视图分析,empid只用单个值,可以看出这里是以empid进行分组,取表中的一行进行分析2.使用T-SQL PIVOT运算符进行透视转化这一行经过转换后可以理解为在在,行=2,列=A 处取两者的SUM(qty)所以查询语句这么写:
SELECT empid,
SUM(CASE WHEN custid = 'A' THEN qty END) AS A,
SUM(CASE WHEN custid = 'B' THEN qty END) AS B,
SUM(CASE WHEN custid = 'C' THEN qty END) AS C,
SUM(CASE WHEN custid = 'D' THEN qty END) AS D
FROM dbo.Orders
GROUP BY empid就可以得到我们想要的结果了
这个查询语句思维这么理解:按empid(做行)分组,当前empid在A列中只有当custid = ‘A’才返回具体的qty值,否则返回为空,再把当前行且custid = ‘A’的qty值聚合起来,其他列也一样。
i.pivot 用法解释
a)<源表或者表表达式> pivot (聚合函数(要聚合的列) for <要旋转为行的列> in (目标列)) as 目标表名
b)细心的朋友也发现的pivot的用法中没有出现要分组的列,这是因为pivot对它的输入表进行了隐式分组,把输入表中的除了聚合列(qty),拓展列(custid)之外的所有列都当成分组元素。所以这边建议大家使用pivot 的时候尽量使用表表达式来作为输入表。避免当源表修改的时候透视转化受影响像填空一样把值填进去之后,pivot查询语句为:
SELECT empid, A, B, C, D
FROM (SELECT empid, custid, qty
FROM dbo.Orders) AS D
PIVOT(SUM(qty) FOR custid IN(A, B, C, D)) AS P;查询结果和另一种方式一样
- SQL SERVER透视转换(行旋转列)
- SQL Server中行列转换
- SQL Server中行列转换
- SQL Server中行列转换
- SQL透视转换
- SQL中的透视转换
- 动态数据透视表pivot table(行转换列)
- SQL Server中行列转换 Pivot UnPivot
- SQL Server中行列转换 Pivot UnPivot
- SQL Server中行列转换 Pivot UnPivot
- SQL Server中行列转换 Pivot UnPivot
- SQL Server中行列转换 Pivot UnPivot
- SQL Server中行列转换 Pivot UnPivot
- SQL Server中行列转换 Pivot UnPivot
- SQL Server中行列转换 Pivot UnPivot
- SQL Server中行列转换 Pivot UnPivot
- SQL Server中行列转换 Pivot UnPivot
- SQL Server中行列转换 Pivot UnPivot
- redis集群搭建之二~使用redis-trib.rb方法
- Emacs 常用配置
- iOS 自定义圆角进度条
- OpenCV —数据持久化: FileStorage类的数据存取操作与示例
- 竖式问题
- SQL SERVER透视转换(行旋转列)
- 串的模式匹配
- Ubuntu Android Studio不能输入中文的解决方案
- Oracle Virtualbox 升级=====>Ubuntu
- Torch7深度学习教程(五)
- 蓝牙(Bluetooth)
- 起航
- Qt之添加应用程序图标
- Memcache 基础教程(php 缓存)