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

透視

簡單的說就是行列轉(zhuǎn)換。假設(shè)一個(gè)銷售表中存放著產(chǎn)品號(hào)，產(chǎn)品折扣，產(chǎn)品價(jià)格三個(gè)列，每一種產(chǎn)品號(hào)可能有多種折扣，每一種折扣只對應(yīng)一個(gè)產(chǎn)品價(jià)格。下面貼出建表語句和插入數(shù)據(jù)語句。

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 

這條語句查詢每一種產(chǎn)品針對每一種折扣的價(jià)錢總和，查詢結(jié)果如下圖1

圖1

從圖中我們可以看出771號(hào)產(chǎn)品有4種折扣，747號(hào)產(chǎn)品有2種折扣，776號(hào)產(chǎn)品有2種折扣?，F(xiàn)在如果我們想知道每一種產(chǎn)品折扣，每一種產(chǎn)品的銷售總價(jià)是多少，如下圖2

圖2

如圖對于折扣0，產(chǎn)品711的總價(jià)是25，對以折扣0.02，產(chǎn)品711的總價(jià)是33等等不再列舉。原來的行是產(chǎn)品號(hào)，現(xiàn)在產(chǎn)品號(hào)變成了列，原來的折扣變成了現(xiàn)在的第一列。這就是數(shù)據(jù)透視的效果。下面我們開看看是這個(gè)效果是如何用語句實(shí)現(xiàn)的。

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 

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

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

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 

輸出的結(jié)果如下圖3

圖3

#p#

逆透視

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

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 

查詢結(jié)果如下圖4：



圖4

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



圖5

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

#p#

延伸閱讀

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

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

這個(gè)語句查出Product表中的制造時(shí)間和平均成本，得到如下的結(jié)果

圖6

如圖可以看到?jīng)]有制造時(shí)間為3天的產(chǎn)品，這里留下一個(gè)伏筆，在透視之后會(huì)出現(xiàn)一個(gè)NULL值。下面使用透視語句對它進(jìn)行行列轉(zhuǎn)換，就是使用0，1,2,3來作為列，使用具體的制造成本作為行數(shù)據(jù)。語句如下

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 

依舊，首先用一個(gè)表值表達(dá)式把要透視的列和透視的項(xiàng)選擇出來，然后使用透視語句針對每一個(gè)項(xiàng)計(jì)算平均成本，最后從這個(gè)透視結(jié)果中選擇出結(jié)果。

結(jié)果如下圖7，我們可以看到制造時(shí)間為3天的產(chǎn)品沒有一個(gè)對應(yīng)的平均成本。

圖7

下面這個(gè)例子稍微復(fù)雜一點(diǎn)。

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

這條語句查詢得到每個(gè)供應(yīng)商和他對應(yīng)的交易號(hào)的個(gè)數(shù)，也就是每個(gè)供應(yīng)商成交的交易次數(shù)。如圖8列舉出部分結(jié)果

圖8

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

select distinct(EmployeeID) from Purchasing.PurchaseOrderHeader 

查詢結(jié)果如圖9

圖9

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

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; 

查詢結(jié)果如下圖10

圖10

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

關(guān)于透視和逆透視的知識(shí)就介紹到這里，謝謝大家！

【編輯推薦】

1. SQL Server如何動(dòng)態(tài)生成分區(qū)腳本
2. 用FOR XML PATH將查詢結(jié)果以XML輸出
3. 淺述SQL Server的Replication技術(shù)創(chuàng)建技巧
4. 簡述SQL Server Replication的常見錯(cuò)誤及其處理
5. 如何在SQL Server 2005中使用作業(yè)實(shí)現(xiàn)備份和特定刪除