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

透視

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

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) 

首先來(lái)看一條查詢語(yǔ)句

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

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

圖1

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

圖2

如圖對(duì)于折扣0，產(chǎn)品711的總價(jià)是25，對(duì)以折扣0.02，產(chǎn)品711的總價(jià)是33等等不再列舉。原來(lái)的行是產(chǎn)品號(hào)，現(xiàn)在產(chǎn)品號(hào)變成了列，原來(lái)的折扣變成了現(xiàn)在的第一列。這就是數(shù)據(jù)透視的效果。下面我們開(kāi)看看是這個(gè)效果是如何用語(yǔ)句實(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)行聚合，這里是對(duì)so.ProductPrice進(jìn)行聚合，聚合針對(duì)so.ProductID進(jìn)行。

在這個(gè)例子中對(duì)三種產(chǎn)品的中的每一種創(chuàng)建一個(gè)列。這個(gè)相當(dāng)于group by，從so表達(dá)式中進(jìn)行數(shù)據(jù)篩選。不過(guò)這里沒(méi)有選出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#

逆透視

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

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

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



圖5

下面我們來(lái)剖析一下上面的語(yǔ)句到底做了些什么。首先是一個(gè)表值函數(shù)(select UnitPriceDiscount,Product711,Product747,Product776 from #Temp1) as up1，這個(gè)表值函數(shù)從透視結(jié)果，也就是臨時(shí)表中，然后針對(duì)每一個(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è)語(yǔ)句，下面來(lái)看看TechNet中的例子。

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

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

圖6

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

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)選擇出來(lái)，然后使用透視語(yǔ)句針對(duì)每一個(gè)項(xiàng)計(jì)算平均成本，最后從這個(gè)透視結(jié)果中選擇出結(jié)果。

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

圖7

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

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

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

圖8

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

select distinct(EmployeeID) from Purchasing.PurchaseOrderHeader 

查詢結(jié)果如圖9

圖9

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

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

可以 簡(jiǎn)單地計(jì)算一下1+4+3+5+4+4+4+5+5+4+5+6+2剛好等于51，分開(kāi)來(lái)看就是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. 簡(jiǎn)述SQL Server Replication的常見(jiàn)錯(cuò)誤及其處理
5. 如何在SQL Server 2005中使用作業(yè)實(shí)現(xiàn)備份和特定刪除