以下的文章主要描述的是SQL Server 2005新功能之PIVOT，在工具的升級中，我個人認為首先我們的看看這個工具，其主要是在哪些功能上得到加強，所以今天我們就來看看SQL2005這個PIVOT吧。PIVOT 關系運算符對表值表達式進行操作以獲得另一個表。

PIVOT 通過將表達式某一列中的唯一值轉換為輸出中的多個列來轉換表。工具的升級，我以為得先看看這個工具在哪些功能上得到加強，今天我們就看看SQL2005這個PIVOT吧。PIVOT 關系運算符對表值表達式進行操作以獲得另一個表。PIVOT 通過將表達式某一列中的唯一值轉換為輸出中的多個列來轉換表值表達式，并在必要時對最終輸出中所需的任何其余的列值執行聚合。

記得我們在SQL2000中要用聚合和CASE語句完成一個行列轉換吧，特別當待轉成列的數據不定時，我們往往構造動態SQL，然后用EXEC來運行。

環境準備：

-- Author: happyflsytone   
-- Version:V1.001   
-- Date:2008-09-18 10:20:53   
-- Test Data: ta   
IF OBJECT_ID('ta') IS NOT NULL   
DROP TABLE ta   
;   
CREATE TABLE ta(id INT,col1 Nvarchar(2),col2 Nvarchar(2),col3 Nvarchar(4),col4 INT)   
;   
INSERT INTO ta   
SELECT 1,'HN','CS','abc',1 UNION ALL   
SELECT 2,'HN','CS','abcd',2 UNION ALL   
SELECT 3,'HN','CD','abcd' ,3UNION ALL   
SELECT 4,'HN','HY','ae' ,4   
;   

我們先來回顧SQL2000的行列轉換,比如我們對上例程把col3轉列顯示，并把col4的和當對應列值。我們分兩種情況來討論：

一、當col3的列值固定就是'abc','abcd','ae'三種情況

SELECT   
col1,   
col2,   
[abc] = SUM(CASE WHEN col3 = 'abc' THEN col4 ELSE 0 END),   
[abcd] = SUM(CASE WHEN col3 = 'abcd' THEN col4 ELSE 0 END),   
[ae] = SUM(CASE WHEN col3 = 'ae' THEN col4 ELSE 0 END)   
FROM ta   
GROUP BY col1,col2   
/*   
col1 col2 abc abcd ae   
HN CD 0 3 0   
HN CS 1 2 0   
HN HY 0 0 4  

(3 行受影響)

二、當col3的列值不固定時就運用動態SQL，其實也就是構造一個sum(CASE WHEN ...)SQL字符串

DECLARE @s varchar(8000)   
SELECT @s = isnull(@s+',   
','') +'['+col3+'] = SUM(CASE WHEN col3 = '''+col3+''' THEN col4 ELSE 0 END)'   
FROM ( SELECT distinct col3 FROM ta) a   
SET @s = 'SELECT   
col1,   
col2,   
'+@s + '   
FROM ta   
GROUP BY   
col1,col2'   
EXEC(@s)   
/*   
col1 col2 abc abcd ae   
HN CD 0 3 0   
HN CS 1 2 0   
HN HY 0 0 4  

(3 行受影響)

我們先輸入這個@S看看是什么東東，只要加上print @s

SELECT   
col1,   
col2,   
[abc] = SUM(CASE WHEN col3 = 'abc' THEN col4 ELSE 0 END),   
[abcd] = SUM(CASE WHEN col3 = 'abcd' THEN col4 ELSE 0 END),   
[ae] = SUM(CASE WHEN col3 = 'ae' THEN col4 ELSE 0 END)   
FROM ta   
GROUP BY   
col1,col2  

其實就是上面我們構造的固定列值的SQL嘛。

好，現在們開始在2005中實現這個功能，先來看看2005的FROM子句的定義(關于如何看這個定義請參照SQL2005的文檔約定及Transate-SQL語法約定)：

[ FROM { <table_source> } [ ,...n ] ]   
<table_source> ::=   
{   
<pivoted_table>   
}   
<pivoted_table> ::=   
table_source PIVOT <pivot_clause> table_alias   
<pivot_clause> ::=   
( aggregate_function ( value_column )   
FOR pivot_column   
IN ( <column_list> )   
)   
<column_list> ::=   
column_name [ , ... ]   

pivot_column 和 value_column 是 PIVOT 運算符使用的組合列。PIVOT 遵循以下過程獲得輸出結果集：

對分組列的 input_table 執行 GROUP BY，為每個組生成一個輸出行。

輸出行中的分組列獲得 input_table 中該組的對應列值。

通過執行以下操作，為每個輸出行生成列列表中的列的值：

針對 pivot_column，對上一步在 GROUP BY 中生成的行另外進行分組。

對于 column_list 中的每個輸出列，選擇滿足以下條件的子組：

pivot_column = CONVERT(<data type of pivot_column>, 'output_column')  

針對此子組上的 aggregate_function 對 value_column 求值，其結果作為相應的 output_column 的值返回。如果該子組為空，SQL Server 2005 將為該 output_column 生成空值。如果聚合函數是 COUNT，且子組為空，則返回零 (0)。

接著我們利用我們開頭的例子來理解一下這個FROM子句，很顯然我們的col4對應上面的value_column,我們還假定列會下固定為這三項，那么列 col3 對應上面的pivot_column,進而我們應該得出[abc],[abcd],[ae]是column_name即我們的輸出列，最后我們只要構造一下table_source就可以了，如何構造這個table_source，顯然pivot_column 和 value_column應該包含在其中，其它就應該是你想要分組的列啦.

我們來總結一下：這個FROM子句是基于 table_source 對 pivot_column 進行透視，table_source 中 pivot_column 和 value_column 列之外的列被稱為透視運算符的組合列,而PIVOT 是對輸入表執行組合列的分組操作，并為每個組返回一行，好，我們試著寫出這個SQL：

SELECT col1,col2,[abc],[abcd],[ae]   
FROM   
(SELECT col1,col2,col3,col4   
FROM ta ) p   
PIVOT   
( SUM (col4)   
FOR col3 IN ([abc],[abcd],[ae])   
)AS unpvt  

我們執行一下看看結果：

/*   
col1 abc abcd ae  

以上的相關內容就是對SQL Server 2005 的新功能的介紹，望你能有所收獲。

【編輯推薦】

1. SQL Server行轉列的什么情況下被用？
2. SQL Server獲取表的容量很簡單！
3. SQL Server排序遇到NULL，不怕不帕!
4. SQL Server 2005兩種快照隔離機制的不同之處
5. SQL Server 2008 FileStream支持“真功夫版”