在今天的文章里我想討論下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 recordsINSERT 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個階段：

1. 分組階段（Grouping Phase）
2. 攤開階段（Spreading Phase）
3. 聚合階段（Aggregation Phase）

在分組階段（Grouping Phase）我們壓縮我們的EAV表為不同的數據庫實體。在這里我們在RecordID列進行一個GROUP BY。在第2階段的，攤開階段（Spreading Phase），我們使用多個CASE表達式來旋轉行為列。最后在聚合階段（Aggregation Phase）我們使用MAX表達式來為每個行和列返回不同值。我們來看下列T-SQL代碼。

 1 -- Pivot the data with a handwritten T-SQL statement. 2 -- Make sure you have an index defined on the grouping column. 3 SELECT 4     RecordID, 5     -- Spreading and aggregation phase 6     MAX(CASE WHEN Element = 'FirstName' THEN Value END) AS 'FirstName', 7     MAX(CASE WHEN Element = 'LastName' THEN Value END) AS 'LastName', 8     MAX(CASE WHEN Element = 'City' THEN Value END) AS 'City', 9     MAX(CASE WHEN Element = 'Country' THEN Value END) AS 'Country'10 FROM EAVTable11 GROUP BY RecordID -- Grouping phase12 GO

從代碼里可以看到，很容易區分每個階段，還有它們如何映射到T-SQL查詢。下圖給你展示了查詢結果，最后我們把行轉為了列。

自SQL Server 2005起（差不多10年前了！），微軟在T-SQL里引入PIVOT運算符。使用那個運算符你可以進行同樣的轉換（行到列），只要一個原生運算符即可。聽起來很簡單，很有前景，不是么？下列代碼顯示了使用原生PIVOT運算符進行同樣的轉換。

 1 -- Perform the same query with the native PIVOT Operator. 2 -- The grouping column is not specified explicitly, it's the remaining column 3 -- that is not referenced in the spreading and aggregation elements. 4 SELECT 5     RecordID, 6     FirstName, 7     LastName, 8     City, 9     Country10 FROM EAVTable11 PIVOT(MAX(Value) FOR Element IN (FirstName, LastName, City, Country)) AS t12 GO

當你執行那個查詢時，你會收到和剛才圖片一樣的結果。但當你看PIVOT運算符語法時，和手動方法相比，你會看到一個很大的區別：

分組元素是你在PIVOT運算符里沒有引用的剩下列。在我們的例子里，我們沒有在PIVOT運算符里沒有引用RecordID列，因此這個列在分組階段（Grouping Phase）被使用。如果我們隨后修改數據庫架構，這會帶來有趣的副作用，例如對基本表增加額外列：

1 -- Add a new column to the table2 ALTER TABLE EAVTable ADD SomeData CHAR(1)3 GO

然后我們對其賦值：

1 UPDATE dbo.EAVTable SET SomeData=LEFT(CAST(Value AS VARCHAR(1)),1)

現在當你執行用PIVOIT運算符的同個查詢時（在那somedata列都有非NULL值），你會拿回完全不同的結果，因為排序階段現在是在RecordID和SomeData列（我們剛加的）上。

相比如果我們重新執行我們剛開始寫的手工T-SQL查詢會發生什么。它還是返回同樣正確的結果。這是在SQL Server里，PIVOT運算符的其中一個最大的副作用：分組元素不能明確定義。為了克服這個問題，最佳實踐是使用只返回需要列的表表達式。使用這個方法，如果你隨后修改表架構還是沒有問題，因從表表達式默認情況下額外的列還是沒有返回。我們來看下列的代碼：

 1 -- Use a table expression to state explicitly which columns you want to  2 -- return from the base table. Therefore you can always control on which 3 -- columns the PIVOT operator is performing the grouping. 4 SELECT 5     RecordID, 6     FirstName, 7     LastName, 8     City, 9     Country10 FROM11 (12     -- Table Expression13     SELECT RecordID, Element, Value FROM EAVTable14 ) AS t15 PIVOT(MAX(Value) FOR Element IN (FirstName, LastName, City, Country)) AS t116 GO

從代碼里可以看到，我通過一個表表達式輸送給PIVOT運算符。而且在表表達式里，你從表里只選擇需要的列。這就意味著以后你可以修改表架構也會破壞PIVOT查詢的結果。

我希望這篇文章已向你展示了在SQL Server里，為什么PIVOT運算符是非常危險的。這個語法本身帶來了非常高效的代碼，但作為副作用你不能直接指定分組元素。因次你應該確保使用一個表表達式來定義輸送給PIVOT運算符的列來保證給出結果的確定性。

用PIVOT運算符你有什么經歷？你是否喜歡它？如果你不喜歡它，你想要什么改變？

感謝關注！