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

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

記得我們在SQL2000中要用聚合和CASE語句完成一個行列轉(zhuǎn)換吧，特別當(dāng)待轉(zhuǎn)成列的數(shù)據(jù)不定時(shí)，我們往往構(gòu)造動態(tài)SQL，然后用EXEC來運(yùn)行。

環(huán)境準(zhǔn)備：

-- 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的行列轉(zhuǎn)換,比如我們對上例程把col3轉(zhuǎn)列顯示，并把col4的和當(dāng)對應(yīng)列值。我們分兩種情況來討論：

一、當(dāng)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 行受影響)

二、當(dāng)col3的列值不固定時(shí)就運(yùn)用動態(tài)SQL，其實(shí)也就是構(gòu)造一個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  

其實(shí)就是上面我們構(gòu)造的固定列值的SQL嘛。

好，現(xiàn)在們開始在2005中實(shí)現(xiàn)這個功能，先來看看2005的FROM子句的定義(關(guān)于如何看這個定義請參照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 運(yùn)算符使用的組合列。PIVOT 遵循以下過程獲得輸出結(jié)果集：

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

輸出行中的分組列獲得 input_table 中該組的對應(yīng)列值。

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

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

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

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

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

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

我們來總結(jié)一下：這個FROM子句是基于 table_source 對 pivot_column 進(jìn)行透視，table_source 中 pivot_column 和 value_column 列之外的列被稱為透視運(yùn)算符的組合列,而PIVOT 是對輸入表執(zhí)行組合列的分組操作，并為每個組返回一行，好，我們試著寫出這個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  

我們執(zhí)行一下看看結(jié)果：

/*   
col1 abc abcd ae  

以上的相關(guān)內(nèi)容就是對SQL Server 2005 的新功能的介紹，望你能有所收獲。

【編輯推薦】

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