如何進(jìn)行SQL中PIVOT行列轉(zhuǎn)換-創(chuàng)新互聯(lián)

這篇文章主要講解了“如何進(jìn)行SQL中PIVOT行列轉(zhuǎn)換”，文中的講解內(nèi)容簡(jiǎn)單清晰，易于學(xué)習(xí)與理解，下面請(qǐng)大家跟著小編的思路慢慢深入，一起來(lái)研究和學(xué)習(xí)“如何進(jìn)行SQL中PIVOT行列轉(zhuǎn)換”吧！

十年專(zhuān)注成都網(wǎng)站制作，成都企業(yè)網(wǎng)站定制，個(gè)人網(wǎng)站制作服務(wù)，為大家分享網(wǎng)站制作知識(shí)、方案，網(wǎng)站設(shè)計(jì)流程、步驟,成功服務(wù)上千家企業(yè)。為您提供網(wǎng)站建設(shè),網(wǎng)站制作,網(wǎng)頁(yè)設(shè)計(jì)及定制高端網(wǎng)站建設(shè)服務(wù),專(zhuān)注于成都企業(yè)網(wǎng)站定制,高端網(wǎng)頁(yè)制作,對(duì)成都石牌坊等多個(gè)領(lǐng)域，擁有多年的網(wǎng)站營(yíng)銷(xiāo)經(jīng)驗(yàn)。

PIVOT通過(guò)將表達(dá)式某一列中的值轉(zhuǎn)換為輸出中的多個(gè)列來(lái)旋轉(zhuǎn)表值表達(dá)式，并在必要時(shí)對(duì)最終輸出中所需的任何其余列值執(zhí)行聚合。UNPIVOT與PIVOT執(zhí)行相反的操作，將表值表達(dá)式的列轉(zhuǎn)換為列值。

通俗簡(jiǎn)單的說(shuō)：PIVOT就是行轉(zhuǎn)列，UNPIVOT就是列傳行

一、PIVOT實(shí)例

1. 建表

建立一個(gè)銷(xiāo)售情況表，其中，year字段表示年份，quarter字段表示季度，amount字段表示銷(xiāo)售額。quarter字段分別用Q1, Q2, Q3, Q4表示一、二、三、四季度。

 CREATE TABLE SalesByQuarter
 ( year INT, -- 年份
  quarter CHAR(2), -- 季度
  amount MONEY -- 總額
 )

2. 填入表數(shù)據(jù)

使用如下程序填入表數(shù)據(jù)。

SET NOCOUNT ON
 DECLARE @index INT
 DECLARE @q INT
 SET @index = 0
 DECLARE @year INT
 while (@index < 30)
 BEGIN
  SET @year = 2005 + (@index % 4)
  SET @q = (CAST((RAND() * 500) AS INT) % 4) + 1
  INSERT INTO SalesByQuarter VALUES (@year, 'Q' + CAST(@q AS CHAR(1)), RAND() * 10000.00)
  SET @index = @index + 1

3、如果我們要比較每年中各季度的銷(xiāo)售狀況，要怎么辦呢？有以下兩種方法：

（1）、使用傳統(tǒng)Select的CASE語(yǔ)句查詢(xún)

在SQL Server以前的版本里，將行級(jí)數(shù)據(jù)轉(zhuǎn)換為列級(jí)數(shù)據(jù)就要用到一系列CASE語(yǔ)句和聚合查詢(xún)。雖然這種方式讓開(kāi)發(fā)人員具有了對(duì)所返回?cái)?shù)據(jù)進(jìn)行高度控制的能力，但是編寫(xiě)出這些查詢(xún)是一件很麻煩的事情。

  SELECT year as 年份
  , sum (case when quarter = 'Q1' then amount else 0 end) 一季度
  , sum (case when quarter = 'Q2' then amount else 0 end) 二季度
  , sum (case when quarter = 'Q3' then amount else 0 end) 三季度
  , sum (case when quarter = 'Q4' then amount else 0 end) 四季度
 FROM SalesByQuarter GROUP BY year ORDER BY year DESC

得到的結(jié)果如下：

（2）、使用PIVOT

由于SQL Server 2005有了新的PIVOT運(yùn)算符，就不再需要CASE語(yǔ)句和GROUP BY語(yǔ)句了。（每個(gè)PIVOT查詢(xún)都涉及某種類(lèi)型的聚合，因此你可以忽略GROUP BY語(yǔ)句。）PIVOT運(yùn)算符讓我們能夠利用CASE語(yǔ)句查詢(xún)實(shí)現(xiàn)相同的功能，但是你可以用更少的代碼就實(shí)現(xiàn)，而且看起來(lái)更漂亮。

SELECT year as 年份, Q1 as 一季度, Q2 as 二季度, Q3 as 三季度, Q4 as 四季度 FROM SalesByQuarter PIVOT (SUM (amount) FOR quarter IN (Q1, Q2, Q3, Q4) ) AS P ORDER BY YEAR DESC

得到的結(jié)果如下：

二、通過(guò)下面一個(gè)實(shí)例詳細(xì)介紹PIVOT的過(guò)程

SELECT [星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日]--這里是PIVOT第三步（選擇行轉(zhuǎn)列后的結(jié)果集的列）這里可以用“*”表示選擇所有列，也可以只選擇某些列(也就是某些天)
FROM WEEK_INCOME --這里是PIVOT第二步驟(準(zhǔn)備原始的查詢(xún)結(jié)果，因?yàn)镻IVOT是對(duì)一個(gè)原始的查詢(xún)結(jié)果集進(jìn)行轉(zhuǎn)換操作，所以先查詢(xún)一個(gè)結(jié)果集出來(lái))這里可以是一個(gè)select子查詢(xún)，但為子查詢(xún)時(shí)候要指定別名，否則語(yǔ)法錯(cuò)誤
PIVOT
(
 SUM(INCOME) for [week] in([星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日])--這里是PIVOT第一步驟，也是核心的地方，進(jìn)行行轉(zhuǎn)列操作。聚合函數(shù)SUM表示你需要怎樣處理轉(zhuǎn)換后的列的值，是總和(sum)，還是平均(avg)還是min,max等等。例如如果week_income表中有兩條數(shù)據(jù)并且其week都是“星期一”，其中一條的income是1000,另一條income是500，那么在這里使用sum，行轉(zhuǎn)列后“星期一”這個(gè)列的值當(dāng)然是1500了。后面的for [week] in([星期一],[星期二]...)中 for [week]就是說(shuō)將week列的值分別轉(zhuǎn)換成一個(gè)個(gè)列，也就是“以值變列”。但是需要轉(zhuǎn)換成列的值有可能有很多，我們只想取其中幾個(gè)值轉(zhuǎn)換成列，那么怎樣取呢？就是在in里面了，比如我此刻只想看工作日的收入，在in里面就只寫(xiě)“星期一”至“星期五”（注意，in里面是原來(lái)week列的值,"以值變列"）?？偟膩?lái)說(shuō)，SUM(INCOME) for [week] in([星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日])這句的意思如果直譯出來(lái)，就是說(shuō)：將列[week]值為"星期一","星期二","星期三","星期四","星期五","星期六","星期日"分別轉(zhuǎn)換成列，這些列的值取income的總和。
)TBL--別名一定要寫(xiě)

三.UNPIVOT

很明顯，UN這個(gè)前綴表明了，它做的操作是跟PIVOT相反的，即列轉(zhuǎn)行。UNPIVOT操作涉及到以下三個(gè)邏輯處理階段。

1，生成副本
2，提取元素
3，刪除帶有NULL的行

UNPIVOT實(shí)例

CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
 Emp3 int, Emp4 int, Emp5 int);
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4);
INSERT INTO pvt VALUES (2,4,1,5,5,5);
INSERT INTO pvt VALUES (3,4,3,5,4,4);
INSERT INTO pvt VALUES (4,4,2,5,5,4);
INSERT INTO pvt VALUES (5,5,1,5,5,5);
GO
--Unpivot the table.
SELECT VendorID, Employee, Orders
FROM 
 (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
 FROM pvt) p
UNPIVOT
 (Orders FOR Employee IN 
  (Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;
GO

上面UNPIVOT實(shí)例的分析

UNPIVOT的輸入是左表表達(dá)式P，第一步，先為P中的行生成多個(gè)副本，在UNPIVOT中出現(xiàn)的每一列，都會(huì)生成一個(gè)副本。因?yàn)檫@里的IN子句有5個(gè)列名稱(chēng)，所以要為每個(gè)來(lái)源行生成5個(gè)副本。結(jié)果得到的虛擬表中將新增一個(gè)列，用來(lái)以字符串格式保存來(lái)源列的名稱(chēng)（for和IN之間的，上面例子是 Employee ）。第二步，根據(jù)新增的那一列中的值從來(lái)源列中提取出與列名對(duì)應(yīng)的行。第三步，刪除掉結(jié)果列值為null的行，完成這個(gè)查詢(xún)。

感謝各位的閱讀，以上就是“如何進(jìn)行SQL中PIVOT行列轉(zhuǎn)換”的內(nèi)容了，經(jīng)過(guò)本文的學(xué)習(xí)后，相信大家對(duì)如何進(jìn)行SQL中PIVOT行列轉(zhuǎn)換這一問(wèn)題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是創(chuàng)新互聯(lián)網(wǎng)站建設(shè)公司，，小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！

本文標(biāo)題：如何進(jìn)行SQL中PIVOT行列轉(zhuǎn)換-創(chuàng)新互聯(lián)
網(wǎng)站URL：http://muchs.cn/article36/dgidpg.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供響應(yīng)式網(wǎng)站、商城網(wǎng)站、品牌網(wǎng)站建設(shè)、網(wǎng)站內(nèi)鏈、靜態(tài)網(wǎng)站、App開(kāi)發(fā)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶(hù)投稿、用戶(hù)轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話(huà)：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)