T-SQL 语言基础：透视转换

透视转换

概念

透视转换是将数据从行的状态旋转为列的状态，通常需要对值进行聚合。这种技术在创建汇总报告和交叉表时非常有用。

• 在 TEMPDB 创建透视所需的基础数据表 Orders。

use tempdb;

if OBJECT_ID('dbo.orders', 'U') is not null	DROP TABLE dbo.Orders;

CREATE TABLE dbo.orders
(
	orderid			INT		NOT NULL,
	orderdate		DATE	NOT NULL,
	empid			INT		NOT NULL,
	custid			VARCHAR(5)	NOT NULL,
	qty				INT		NOT NULL,
	CONSTRAINT PK_Orders PRIMARY KEY(orderid)
);

INSERT INTO dbo.orders 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);

select * from dbo.orders

输出：

– 生成报表，包含雇员和客户组合之间的总订货量

SELECT empid, custid, SUM(qty) AS sumqty
FROM dbo.orders
GROUP BY empid, custid;

输出：

透视转换处理逻辑

透视转换涉及三个逻辑处理阶段，每个阶段都有相关的元素：
-- 1. 分组阶段：处理相关的分组或行元素
-- 2. 扩展阶段：处理相关的扩展或列元素
-- 3. 聚合阶段：处理相关的聚合元素和聚合函数

1. 透视转换实现一

使用标准 SQL 进行透视转换

-- 分组阶段 使用 GROUP BY 子句实现
-- 扩展阶段通过在 SELECT 子句为每个目标指定 CASE 表达式实现
	-- 需要事先知道每个扩展元素的取值，并为每个值指定一个单独的 CASE 表达式
	-- 需要对4个客户A，B，C，D的订货量进行扩展，需要用 4 个 CASE 表达式
		-- CASE WHEN custid = 'A' THEN qty END
		-- 聚合阶段为每个 CASE 表达式结果应用相关的聚合函数 本例使用 SUM
			-- SUM(CASE WHEN custid = 'A' THEN qty END) AS A

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;

输出：

2. 透视转换实现二

使用 T-SQL PIVOT 运算符进行透视转换

	-- PIVOT 也是在查询 FROM 子句上下文中执行操作。
			-- 同样涉及三个逻辑处理阶段（分组，扩展及聚合）和同样的透视转换原酸
			-- 不同的是： SQL Server 原生语法

				SELECT empid, A, B, C, D
				FROM (
					SELECT empid, custid, qty
					FROM dbo.orders) D
				PIVOT(SUM(qty) FOR custid IN (A, B, C, D)) p;

输出：

PIVOT 运算符的圆括号内要指定聚合函数（SUM），聚合元素（qty），扩展元素（custid）及目标名称列表（A, B, C, D），PIVOT 圆括号后指定别名  
PIVOT 运算符不需要显式地指定分组元素

总结

透视转换功能强大，用于转换和汇总数据。可以高效地创建复杂报告并执行高级数据分析。

引用

• SQL Server Documentation

数据库脚本下载

TSQLFundamentals2008