SQL查詢時，我們可能會用到T-SQL透視和逆透視的功能，比如我們對銷售表中的列進行查詢時就用到了。透視運算符要使用子查詢中的數據進行聚合運算，然后再輸出。本文通過實例詳細講述了這一過程，下面先說透視。

透視

簡單的說就是行列轉換。假設一個銷售表中存放著產品號，產品折扣，產品價格三個列，每一種產品號可能有多種折扣，每一種折扣只對應一個產品價格。下面貼出建表語句和插入數據語句。

create table SalesOrderDetail(  
 
ProductID int /*unique多謝wuu00的提醒*/,  
 
3 UnitPriceDiscount float,  
 
4 ProductPrice float  
 
)  
 
insert into SalesOrderDetail values  
 
(711,.00,12),  
 
(711,.00,13),  
 
(711,.02,17),  
 
(711,.02,16),  
 
(711,.05,19),  
 
(711,.05,20),  
 
(711,.10,21),  
 
(711,.10,22),  
 
(711,.15,23),  
 
(711,.15,24),  
 
(747,.00,41),  
 
(747,.00,42),  
 
(747,.02,45),  
 
(747,.02,46),  
 
(776,.20,50),  
 
(776,.20,49),  
 
(776,.35,52),  
 
(776,.35,53) 

首先來看一條查詢語句

select ProductID,UnitPriceDiscount,SUM(ProductPrice) as SumPrice   
 
from SalesOrderDetail   
 
group by ProductID,UnitPriceDiscount  
 
order by ProductID,UnitPriceDiscount 

這條語句查詢每一種產品針對每一種折扣的價錢總和，查詢結果如下圖1

圖1

從圖中我們可以看出771號產品有4種折扣，747號產品有2種折扣，776號產品有2種折扣。現在如果我們想知道每一種產品折扣，每一種產品的銷售總價是多少，如下圖2

圖2

如圖對于折扣0，產品711的總價是25，對以折扣0.02，產品711的總價是33等等不再列舉。原來的行是產品號，現在產品號變成了列，原來的折扣變成了現在的第一列。這就是數據透視的效果。下面我們開看看是這個效果是如何用語句實現的。

select * from   
 
(select sod.ProductPrice,sod.ProductID,sod.UnitPriceDiscount from SalesOrderDetail sod) so  
 
pivot  
 
(  
 
sum(so.ProductPrice) for so.ProductID in([711],[747],[776])  
 
6 ) as pt  
 
order by UnitPriceDiscount 

首選創建子查詢(select sod.ProductPrice,sod.ProductID,sod.UnitPriceDiscount from SalesOrderDetail sod) so ，透視運算符要使用這個子查詢中的數據進行聚合運算，此外輸出顯示也要用到子查詢中的列。代碼生成一個別名為so的表值表達式。在這個表中使用pivot在特定的列上進行聚合，這里是對so.ProductPrice進行聚合，聚合針對so.ProductID進行。

在這個例子中對三種產品的中的每一種創建一個列。這個相當于group by，從so表達式中進行數據篩選。不過這里沒有選出ProductPrice，僅僅生成每行三個列，每一種產品為一個列的結果集。因此帶有povit的表值表達式生成一個臨時的結果集，將這個結果集命名為pt，使用這個結果集生成我們需要的輸出。如果想要得到一個更加合適的列名可以修改篩選條件。如下：

select pt.UnitPriceDiscount,[711] as Product711,[747] as Product747,[776] as Product747 from  
 
(select sod.ProductPrice,sod.ProductID,sod.UnitPriceDiscount from SalesOrderDetail sod) so  
 
pivot  
 
(  
 
sum(so.ProductPrice) for so.ProductID in([711],[747],[776])  
 
) as pt  
 
order by UnitPriceDiscount 

輸出的結果如下圖3

圖3

#p#

逆透視

這次我們首先看語句和查詢結果再分析，語句如下：

select ProductID,UnitPriceDiscount,ProductPrice  
 
from  
 
(select UnitPriceDiscount,Product711,Product747,Product776 from #Temp1) as up1  
 
unpivot(ProductPrice for ProductID in(Product711,Product747,Product776)) as up2  
 
5 order by ProductID 

查詢結果如下圖4：



圖4

首先我們來看看逆透視得到了一個什么樣的結果。對于每一種產品的每一種折扣查詢得到他們的合計售價，這個和上面圖1中的結果是一樣的，是的，它和透視之前的結果是相同的。逆透視和透視并不是完全相反。Pivot會執行聚合，把可能存在的多個行合并輸出得到一行。由于已經進行了合并，unpivot無法重新生成原始的表值表達式，unpivot輸入中的null值將在輸出中消失，盡管在pivot操作之前輸入中可能存在原始的null值。如圖5是他們的比較。在圖中我們可以看到NULL值下面一個圖中沒有NULL值，剛好有9行。下圖把他們放在一起比較。



圖5

下面我們來剖析一下上面的語句到底做了些什么。首先是一個表值函數(select UnitPriceDiscount,Product711,Product747,Product776 from #Temp1) as up1，這個表值函數從透視結果，也就是臨時表中，然后針對每一個產品號進行逆透視：unpivot(ProductPrice for ProductID in(Product711,Product747,Product776)) as up2，然后從逆透視結果中選擇ProductID ,ProductPrice，從表值函數中選擇UnitPriceDiscount。

#p#

延伸閱讀

一個例子還不足以讓我們理解這個語句，下面來看看TechNet中的例子。

SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost FROM Production.Product  
 
GROUP BY DaysToManufacture; 

這個語句查出Product表中的制造時間和平均成本，得到如下的結果

圖6

如圖可以看到沒有制造時間為3天的產品，這里留下一個伏筆，在透視之后會出現一個NULL值。下面使用透視語句對它進行行列轉換，就是使用0，1,2,3來作為列，使用具體的制造成本作為行數據。語句如下

select   
 
'AverageCost' as Cost_Sorted_By_Production_Days,  
 
[0],[1],[3],[4]  
 
from  
 
(select DaysToManufacture,StandardCost from Production.Product) as SourceTable  
 
6 pivot  
 
(avg(StandardCost) for DaysToManufacture in ([0],[1],[3],[4])) as PivotTable 

依舊，首先用一個表值表達式把要透視的列和透視的項選擇出來，然后使用透視語句針對每一個項計算平均成本，最后從這個透視結果中選擇出結果。

結果如下圖7，我們可以看到制造時間為3天的產品沒有一個對應的平均成本。

圖7

下面這個例子稍微復雜一點。

SELECT VendorID,count(PurchaseOrderID) as PurchaseCunt  
 
FROM Purchasing.PurchaseOrderHeader group by VendorID 

這條語句查詢得到每個供應商和他對應的交易號的個數，也就是每個供應商成交的交易次數。如圖8列舉出部分結果

圖8

從圖中我們可以看到供應商1共成交51比交易，供應商2共成交51筆交易。如果我們想查出這些交易分別是和那些雇員成交的應該怎么寫呢？首先我們來看看表中全部的雇員情況。

select distinct(EmployeeID) from Purchasing.PurchaseOrderHeader 

查詢結果如圖9

圖9

如上圖我們可以看到共有12個雇員有成交記錄。對于這些雇員，如下查詢語句

SELECT   
 
VendorID,  
 
[164] AS Emp164,  
 
[198] AS Emp198,  
 
[223] AS Emp223,  
 
[231] AS Emp231,  
 
[233] AS Emp233,  
 
[238] as Emp238,  
 
[241] as Emp241,  
 
[244] as Emp244,  
 
[261] as Emp261,  
 
[264] as Emp264,  
 
[266] as Emp266,  
 
[274] as Emp274  
 
15 FROM   
 
(SELECT PurchaseOrderID,EmployeeID,VendorID  
 
FROM Purchasing.PurchaseOrderHeader) p  
 
PIVOT  
 
(  
 
COUNT (PurchaseOrderID)  
 
FOR EmployeeID IN  
 
( [164], [198], [223], [231],[233],[238],[241],[244],[261],[264],[266],[274])  
 
) AS pvt  
 
24 ORDER BY pvt.VendorID; 

查詢結果如下圖10

圖10

可以 簡單地計算一下1+4+3+5+4+4+4+5+5+4+5+6+2剛好等于51，分開來看就是1號供應商分別和164號雇員成交4比記錄，和198號雇員成交3比記錄等等。

關于透視和逆透視的知識就介紹到這里，謝謝大家！

【編輯推薦】

1. SQL Server如何動態生成分區腳本
2. 用FOR XML PATH將查詢結果以XML輸出
3. 淺述SQL Server的Replication技術創建技巧
4. 簡述SQL Server Replication的常見錯誤及其處理
5. 如何在SQL Server 2005中使用作業實現備份和特定刪除