????? UNPIVOT運算符相對于PIVOT運算符，它執(zhí)行與PIVOT相反的操作，即將列轉換到行。需要注意的是UNPIVOT運算符并不完全是PIVOT的逆向操作。因為執(zhí)行PIVOT將會執(zhí)行一次聚合，從而將多個可能的行合并為輸出中的單個行。而 UNPIVOT 無法重現原始表值表達式的結果，因為行已經被合并了。另外，UNPIVOT 的輸入中的空值不會顯示在輸出中，而在執(zhí)行 PIVOT 操作之前，輸入中可能有原始的空值。

????? 關于PIVOT運算符的使用，請參考： SQL server 2005 PIVOT運算符的使用 。

一、UNPIVOT的語法

      二、UNPIVOT的使用例子
    

???? 我們使用了 SQL server 2005 PIVOT運算符的使用 文章中的例子。如下所示，從NorthWind數據庫生成數據源及PIVOT表。

1.靜態(tài)UNPIVOT的用法

USE tempdb GO SELECT YEAR(OrderDate) AS [Year] --從NorthWind提取演示示例所需數據 ,CustomerID ,od.Quantity INTO dbo.Orders FROM NorthWind..Orders AS o JOIN NorthWind..[Order Details] AS od ON o.OrderID = od.OrderID WHERE o.CustomerID IN ('BONAP','BOTTM','ANTON') AND od.Quantity > 20; SELECT * FROM dbo.Orders; /*以下列出部分結果集，年的數據省略 Year CustomerID Quantity ----------- ---------- -------- 1996 BONAP 40 1996 BONAP 50 1996 BONAP 24 1996 ANTON 24 1996 BOTTM 30 1997 BOTTM 49 1997 BOTTM 25 1997 BOTTM 40 1997 BOTTM 50 1997 BOTTM 50 1997 BOTTM 30 */ --1->.生成PIVOT數據 SELECT CustomerID ,[1996],[1997],[1998] INTO #pivot_result FROM dbo.Orders PIVOT ( SUM(Quantity) FOR [Year] IN ([1996],[1997],[1998]) )x /* Result: CustomerID 1996 1997 1998 ---------- ----------- ----------- ----------- ANTON 24 205 NULL BONAP 114 356 122 BOTTM 30 389 292 */ --2->.生成UNPIVOT數據,實現列到行的轉換 SELECT CustomerID ,Year ,Quantity FROM #pivot_result UNPIVOT ( Quantity FOR Year IN ([1996],[1997],[1998]) ) x /* Result: CustomerID Year Quantity ---------- ----------- ----------- ANTON 1996 24 ANTON 1997 205 BONAP 1996 114 BONAP 1997 356 BONAP 1998 122 BOTTM 1996 30 BOTTM 1997 389 BOTTM 1998 292 */ --從上面的結果中我們看出CustomerID為ANTON用戶在年，Quantity的值為NULL，即沒有訂單，使用UNPIOVT后忽略掉了NULL值。 --注意UNPIVOT后并沒有回到PIVOT之前的數據，因為實現PIVOT后數據已經被匯總。 --3->.在UNPIVOT后的結果中重現NULL值 SELECT CustomerID ,Year ,CASE WHEN Quantity = 0 THEN NULL ELSE Quantity END AS Quantity --還原為NULL的值 FROM ( SELECT CustomerID,Year,Quantity FROM ( SELECT CustomerID, ISNULL([1996],0) AS [1996] --對年份列增加了一個ISNULL的判斷 ,ISNULL([1997],0) AS [1997] ,ISNULL([1998],0) AS [1998] FROM #pivot_result) AS pivot_result UNPIVOT ( Quantity FOR Year IN ([1996],[1997],[1998]) ) x )y /* CustomerID Year Quantity ---------- ------- ----------- ANTON 1996 24 ANTON 1997 205 ANTON 1998 NULL --NULL值重現 BONAP 1996 114 BONAP 1997 356 BONAP 1998 122 BOTTM 1996 30 BOTTM 1997 389 BOTTM 1998 292 */

2.動態(tài)UNPIVOT的用法

USE tempd; GO DECLARE @UnpivotColHeader NVARCHAR(MAX) ,@UnpivotTableSQL NVARCHAR(MAX); SELECT @UnpivotColHeader = --將列標題轉換為行值 STUFF( (SELECT N','+ QUOTENAME(x) AS [text()] FROM ( SELECT COLUMN_NAME AS x FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = N'dbo' AND TABLE_NAME = N'pivot_result' AND COLUMN_NAME NOT IN(N'CustomerID') ) AS x ORDER BY x FOR XML PATH('')), 1,1,N''); SET @UnpivotTableSQL = 'SELECT CustomerID ,Year ,CASE WHEN Quantity = 0 THEN NULL ELSE Quantity END AS Quantity FROM ( SELECT CustomerID,Year,Quantity FROM ( SELECT CustomerID, ISNULL([1996],0) AS [1996] ,ISNULL([1997],0) AS [1997] ,ISNULL([1998],0) AS [1998] FROM pivot_result) AS pivot_result UNPIVOT ( Quantity FOR Year IN (' + @UnpivotColHeader + ') ) x )y' + N';'; EXEC sp_executesql @UnpivotTableSQL; --結果同上

三、總結

1.UNPIVOT操作符的第一個輸入是保存被旋轉屬性值(Quantity)的目標列名稱。

2.FOR關鍵字，指定保存被旋轉列名稱(Year)的目標列名稱。

3.在IN子句的圓括號，指定要旋轉的源列名稱([1996],[1997],[1998])。

?

PIVOT運算符的使用，請參閱： http://blog.csdn.net/robinson_0612/archive/2010/03/16/5385117.aspx

SQL server 2005 UNPIVOT運算符的使用