?
透視
今天抽一點時間來看看透視和逆透視語句，簡單的說就是行列轉換。假設一個銷售表中存放著產品號，產品折扣，產品價格三個列，每一種產品號可能有多種折扣，每一種折扣只對應一個產品價格。下面貼出建表語句和插入數據語句。
1
create
table
SalesOrderDetail(
2
ProductID
int
/*
unique多謝
wuu00
的提醒*/
,
3
UnitPriceDiscount
float
,
4
ProductPrice
float
5
)
6
?
insert
into
SalesOrderDetail
values
7
(
711
,.
00
,
12
),
8
(
711
,.
00
,
13
),
9
(
711
,.
02
,
17
),
10
(
711
,.
02
,
16
),
11
(
711
,.
05
,
19
),
12
(
711
,.
05
,
20
),
13
(
711
,.
10
,
21
),
14
(
711
,.
10
,
22
),
15
(
711
,.
15
,
23
),
16
(
711
,.
15
,
24
),
17
(
747
,.
00
,
41
),
18
(
747
,.
00
,
42
),
19
(
747
,.
02
,
45
),
20
(
747
,.
02
,
46
),
21
(
776
,.
20
,
50
),
22
(
776
,.
20
,
49
),
23
(
776
,.
35
,
52
),
24
(
776
,.
35
,
53
)
首先來看一條查詢語句
1
select
ProductID,UnitPriceDiscount,
SUM
(ProductPrice)
as
SumPrice
2
?
from
SalesOrderDetail
3
?
group
by
ProductID,UnitPriceDiscount
4
order
by
ProductID,UnitPriceDiscount
這條語句查詢每一種產品針對每一種折扣的價錢總和，查詢結果如下圖1
圖1
從圖中我們可以看出771號產品有4種折扣，747號產品有2種折扣，776號產品有2種折扣?，F在如果我們想知道每一種產品折扣，每一種產品的銷售總價是多少，如下圖2
圖2
如圖對于折扣0，產品711的總價是25，對以折扣0.02，產品711的總價是33等等不再列舉。原來的行是產品號，現在產品號變成了列，原來的折扣變成了現在的第一列。這就是數據透視的效果。下面我們開看看是這個效果是如何用語句實現的。
1
select
*
from
2
(
select
sod.ProductPrice,sod.ProductID,sod.UnitPriceDiscount
from
SalesOrderDetail sod) so
3
pivot
4
(
5
sum
(so.ProductPrice)
for
so.ProductID
in
(
[
711
]
,
[
747
]
,
[
776
]
)
6
)
as
pt
7
order
by
UnitPriceDiscount
首選創(chuàng)建子查詢(select sod.ProductPrice,sod.ProductID,sod.UnitPriceDiscount from SalesOrderDetail sod) so ，透視運算符要使用這個子查詢中的數據進行聚合運算，此外輸出顯示也要用到子查詢中的列。代碼生成一個別名為so的表值表達式。在這個表中使用pivot在特定的列上進行聚合，這里是對so.ProductPrice進行聚合，聚合針對so.ProductID進行。在這個例子中對三種產品的中的每一種創(chuàng)建一個列。這個相當于group by，從so表達式中進行數據篩選。不過這里沒有選出ProductPrice，僅僅生成每行三個列，每一種產品為一個列的結果集。因此帶有povit的表值表達式生成一個臨時的結果集，將這個結果集命名為pt，使用這個結果集生成我們需要的輸出。如果想要得到一個更加合適的列名可以修改篩選條件。如下：
1
select
pt.UnitPriceDiscount,
[
711
]
as
Product711,
[
747
]
as
Product747,
[
776
]
as
Product747
from
2
(
select
sod.ProductPrice,sod.ProductID,sod.UnitPriceDiscount
from
SalesOrderDetail sod) so
3
pivot
4
(
5
sum
(so.ProductPrice)
for
so.ProductID
in
(
[
711
]
,
[
747
]
,
[
776
]
)
6
)
as
pt
7
order
by
UnitPriceDiscount
輸出的結果如下圖3
圖3
逆透視
這次我們首先看語句和查詢結果再分析，語句如下：
? ?
1
select
ProductID,UnitPriceDiscount,ProductPrice
2
from
3
(
select
UnitPriceDiscount,Product711,Product747,Product776
from
#Temp1)
as
up1
4
unpivot(ProductPrice
for
ProductID
in
(Product711,Product747,Product776))
as
up2
5
order
by
ProductID
查詢結果如下圖4：
?
? 圖4
?
?首先我們來看看逆透視得到了一個什么樣的結果。對于每一種產品的每一種折扣查詢得到他們的合計售價，這個和上面圖1中的結果是一樣的，是的，它和透視之前的結果是相同的。逆透視和透視并不是完全相反。Pivot會執(zhí)行聚合，把可能存在的多個行合并輸出得到一行。由于已經進行了合并，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。
延伸閱讀
一個例子還不足以讓我們理解這個語句，下面來看看TechNet中的例子。
SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost FROM Production.Product
GROUP BY DaysToManufacture;
這個語句查出Product表中的制造時間和平均成本，得到如下的結果
圖6
如圖可以看到沒有制造時間為3天的產品，這里留下一個伏筆，在透視之后會出現一個NULL值。下面使用透視語句對它進行行列轉換，就是使用0，1,2,3來作為列，使用具體的制造成本作為行數據。語句如下
?
1
select
2
'
AverageCost
'
as
Cost_Sorted_By_Production_Days,
3
[
0
]
,
[
1
]
,
[
3
]
,
[
4
]
4
from
5
(
select
DaysToManufacture,StandardCost
from
Production.Product)
as
SourceTable
6
pivot
7
(
avg
(StandardCost)
for
DaysToManufacture
in
(
[
0
]
,
[
1
]
,
[
3
]
,
[
4
]
))
as
PivotTable
依舊，首先用一個表值表達式把要透視的列和透視的項選擇出來，然后使用透視語句針對每一個項計算平均成本，最后從這個透視結果中選擇出結果。
結果如下圖7，我們可以看到制造時間為3天的產品沒有一個對應的平均成本。
圖7
?
下面這個例子稍微復雜一點。
1
SELECT
VendorID,
count
(PurchaseOrderID)
as
PurchaseCunt
2
FROM
Purchasing.PurchaseOrderHeader
group
by
VendorID
這條語句查詢得到每個供應商和他對應的交易號的個數，也就是每個供應商成交的交易次數。如圖8列舉出部分結果
圖8
從圖中我們可以看到供應商1共成交51比交易，供應商2共成交51筆交易。如果我們想查出這些交易分別是和那些雇員成交的應該怎么寫呢？首先我們來看看表中全部的雇員情況。
select distinct(EmployeeID) from Purchasing.PurchaseOrderHeader
查詢結果如圖9
圖9
如上圖我們可以看到共有12個雇員有成交記錄。對于這些雇員，如下查詢語句
?
1
SELECT
2
VendorID,
3
[
164
]
AS
Emp164,
4
[
198
]
AS
Emp198,
5
[
223
]
AS
Emp223,
6
[
231
]
AS
Emp231,
7
[
233
]
AS
Emp233,
8
[
238
]
as
Emp238,
9
[
241
]
as
Emp241,
10
[
244
]
as
Emp244,
11
[
261
]
as
Emp261,
12
[
264
]
as
Emp264,
13
[
266
]
as
Emp266,
14
[
274
]
as
Emp274
15
FROM
16
(
SELECT
PurchaseOrderID,EmployeeID,VendorID
17
FROM
Purchasing.PurchaseOrderHeader) p
18
PIVOT
19
(
20
COUNT
(PurchaseOrderID)
21
FOR
EmployeeID
IN
22
(
[
164
]
,
[
198
]
,
[
223
]
,
[
231
]
,
[
233
]
,
[
238
]
,
[
241
]
,
[
244
]
,
[
261
]
,
[
264
]
,
[
266
]
,
[
274
]
)
23
)
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比記錄等等。
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
