?
透視
今天抽一點(diǎn)時(shí)間來看看透視和逆透視語(yǔ)句，簡(jiǎn)單的說就是行列轉(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ǔ)句。
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
)
首先來看一條查詢語(yǔ)句
1
select
ProductID,UnitPriceDiscount,
SUM
(ProductPrice)
as
SumPrice
2
?
from
SalesOrderDetail
3
?
group
by
ProductID,UnitPriceDiscount
4
order
by
ProductID,UnitPriceDiscount
這條語(yǔ)句查詢每一種產(chǎn)品針對(duì)每一種折扣的價(jià)錢總和，查詢結(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等等不再列舉。原來的行是產(chǎn)品號(hào)，現(xiàn)在產(chǎn)品號(hào)變成了列，原來的折扣變成了現(xiàn)在的第一列。這就是數(shù)據(jù)透視的效果。下面我們開看看是這個(gè)效果是如何用語(yǔ)句實(shí)現(xiàn)的。
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 ，透視運(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ù)篩選。不過這里沒有選出ProductPrice，僅僅生成每行三個(gè)列，每一種產(chǎn)品為一個(gè)列的結(jié)果集。因此帶有povit的表值表達(dá)式生成一個(gè)臨時(shí)的結(jié)果集，將這個(gè)結(jié)果集命名為pt，使用這個(gè)結(jié)果集生成我們需要的輸出。如果想要得到一個(gè)更加合適的列名可以修改篩選條件。如下：
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
輸出的結(jié)果如下圖3
圖3
逆透視
這次我們首先看語(yǔ)句和查詢結(jié)果再分析，語(yǔ)句如下：
? ?
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
查詢結(jié)果如下圖4：
?
? 圖4
?
?首先我們來看看逆透視得到了一個(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è)圖中沒有NULL值，剛好有9行。下圖把他們放在一起比較。
?
?
? ??圖5
下面我們來剖析一下上面的語(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。
延伸閱讀
一個(gè)例子還不足以讓我們理解這個(gè)語(yǔ)句，下面來看看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來作為列，使用具體的制造成本作為行數(shù)據(jù)。語(yǔ)句如下
?
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
依舊，首先用一個(gè)表值表達(dá)式把要透視的列和透視的項(xiàng)選擇出來，然后使用透視語(yǔ)句針對(duì)每一個(gè)項(xiàng)計(jì)算平均成本，最后從這個(gè)透視結(jié)果中選擇出結(jié)果。
結(jié)果如下圖7，我們可以看到制造時(shí)間為3天的產(chǎn)品沒有一個(gè)對(duì)應(yīng)的平均成本。
圖7
?
下面這個(gè)例子稍微復(fù)雜一點(diǎn)。
1
SELECT
VendorID,
count
(PurchaseOrderID)
as
PurchaseCunt
2
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)該怎么寫呢？首先我們來看看表中全部的雇員情況。
select distinct(EmployeeID) from Purchasing.PurchaseOrderHeader
查詢結(jié)果如圖9
圖9
如上圖我們可以看到共有12個(gè)雇員有成交記錄。對(duì)于這些雇員，如下查詢語(yǔ)句
?
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;
查詢結(jié)果如下圖10
圖10
可以 簡(jiǎn)單地計(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比記錄等等。
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號(hào)聯(lián)系： 360901061
您的支持是博主寫作最大的動(dòng)力，如果您喜歡我的文章，感覺我的文章對(duì)您有幫助，請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長(zhǎng)非常感激您！手機(jī)微信長(zhǎng)按不能支付解決辦法：請(qǐng)將微信支付二維碼保存到相冊(cè)，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
