SQL Server裡PIVOT運算子的“紅顏禍水”

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

1. 分組階段（Grouping Phase）
2. 攤開階段（Spreading Phase）
3. 聚合階段（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

然後我們對其賦值：

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

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

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

 -- Use a table expression to state explicitly which columns you want to 
  -- return from the base table. Therefore you can always control on which
  -- columns the PIVOT operator is performing the grouping.
  SELECT
  RecordID,
  FirstName,
  LastName,
  City,
  Country
  FROM
  (
  -- Table Expression
  SELECT RecordID, Element, Value FROM EAVTable
  ) AS t
  PIVOT(MAX(Value) FOR Element IN (FirstName, LastName, City, Country)) AS t1
  GO

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

小結

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

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