在今天的文章里我想讨论下SQL Server里一个特别的T-SQL语言结构——自SQL Server 2005引入的PIVOT运算符。我经常引用这个与语言结构是SQL Server里最危险的一个——很快你就会知道为什么。在我们进入特定问题和陷阱前，首先我想给你下使用SQL Server里的PIVOT能实现什么的一个基本概述。
概述
SQL Server里PIVOT运算符背后的基本思想是在T-SQL查询期间，你可以旋转行为列。运算符本身是SQL Server 2005后引入的，主要用在基于建立在实体属性值模型（Entity Attribute Value model (EAV)）原则上的数据库。EAM模型背后的想法是你可以扩展数据库实体，而不需要进行数据库架构的修改。因此EAV模型存储实体的所有属性以键/值对存储在一个表里。我们来看下面一个简单的键/值对模型的表。
CREATE TABLE EAVTable ( RecordID INT NOT NULL, Element CHAR(100) NOT NULL, Value SQL_VARIANT NOT NULL, PRIMARY KEY (RecordID, Element) ) GO -- Insert some records INSERT INTO EAVTable (RecordID, Element, Value) VALUES (1, 'FirstName', 'Woody'), (1, 'LastName', 'Tu'), (1, 'City', 'Linhai'), (1, 'Country', 'China'), (2, 'FirstName', 'Bill'), (2, 'LastName', 'Gates'), (2, 'City', 'Seattle'), (2, 'Country', 'USA') GO |
如你所见，我们插入2个数据库实体到表里，每个实体包含多个属性。在表里每个属性只是额外的记录。如果你像扩展实体更多的属性，你只插入额外的记录到表里，而没有必要进行数据库架构修改——这就是开放数据库架构的“威力”……
查询这样的EAV表显然很困难，因为你处理的是平键/值对的数据结构。因此你要旋转表内容，行旋转为列。你可以进行用自带的PIVOT运算符进行这个旋转，或者通过传统的CASE表达式进行纯手工来实现。在我们进入PIVOT细节前，我想给你展示下通过手工使用T-SQL和一些CASE表达式来实现。如果你手工进行旋转，你的T-SQL查询需要实现3个阶段：
分组阶段（Grouping Phase）
摊开阶段（Spreading Phase）
聚合阶段（Aggregation Phase）
在分组阶段（Grouping Phase）我们压缩我们的EAV表为不同的数据库实体。在这里我们在RecordID列进行一个GROUP BY。在第2阶段的，摊开阶段（Spreading Phase），我们使用多个CASE表达式来旋转行为列。最后在聚合阶段（Aggregation Phase）我们使用MAX表达式来为每个行和列返回不同值。我们来看下列T-SQL代码。
-- Pivot the data with a handwritten T-SQL statement. -- Make sure you have an index defined on the grouping column. SELECT RecordID, -- Spreading and aggregation phase MAX(CASE WHEN Element = 'FirstName' THEN Value END) AS 'FirstName', MAX(CASE WHEN Element = 'LastName' THEN Value END) AS 'LastName', MAX(CASE WHEN Element = 'City' THEN Value END) AS 'City', MAX(CASE WHEN Element = 'Country' THEN Value END) AS 'Country' FROM EAVTable GROUP BY RecordID -- Grouping phase GO |
从代码里可以看到，很容易区分每个阶段，还有它们如何映射到T-SQL查询。下图给你展示了查询结果，最后我们把行转为了列。
PIVOT运算符
自SQL Server 2005起（差不多10年前了！），微软在T-SQL里引入PIVOT运算符。使用那个运算符你可以进行同样的转换（行到列），只要一个原生运算符即可。听起来很简单，很有前景，不是么？下列代码显示了使用原生PIVOT运算符进行同样的转换。
-- Perform the same query with the native PIVOT operator.
-- The grouping column is not specified explicitly, it's the remaining column
-- that is not referenced in the spreading and aggregation elements.
SELECT
RecordID,
FirstName,
LastName,
City,
Country
FROM EAVTable
PIVOT(MAX(Value) FOR Element IN (FirstName, LastName, City, Country)) AS t
GO
当你执行那个查询时，你会收到和刚才图片一样的结果。但当你看PIVOT运算符语法时，和手动方法相比，你会看到一个很大的区别：
你只能指定分摊和聚合元素！不能明确定义分组元素！
分组元素是你在PIVOT运算符里没有引用的剩下列。在我们的例子里，我们没有在PIVOT运算符里没有引用RecordID列，因此这个列在分组阶段（Grouping Phase）被使用。如果我们随后修改数据库架构，这会带来有趣的副作用，例如对基本表增加额外列：
-- Add a new column to the table
ALTER TABLE EAVTable ADD SomeData CHAR(1)
GO
