主頁 > 知識庫 > 深入SQL中PIVOT 行列轉(zhuǎn)換詳解

深入SQL中PIVOT 行列轉(zhuǎn)換詳解

熱門標簽:美國地圖標注軟件下載 怎么修改高德地圖標注 合肥crm外呼系統(tǒng)加盟 西安電話自動外呼系統(tǒng) 硅基電話機器人官網(wǎng) 長沙外呼系統(tǒng)平臺 電話機器人怎么看余額 城市地圖標志怎么標注 漯河電銷回撥外呼系統(tǒng)

PIVOT通過將表達式某一列中的唯一值轉(zhuǎn)換為輸出中的多個列來旋轉(zhuǎn)表值表達式,并在必要時對最終輸出中所需的任何其余列值執(zhí)行聚合。UNPIVOT與PIVOT執(zhí)行相反的操作,將表值表達式的列轉(zhuǎn)換為列值。

通俗簡單的說:PIVOT就是行轉(zhuǎn)列,UNPIVOT就是列傳行

一、PIVOT實例

1. 建表

建立一個銷售情況表,其中,year字段表示年份,quarter字段表示季度,amount字段表示銷售額。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、如果我們要比較每年中各季度的銷售狀況,要怎么辦呢?有以下兩種方法:

(1)、使用傳統(tǒng)Select的CASE語句查詢

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

  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運算符,就不再需要CASE語句和GROUP BY語句了。(每個PIVOT查詢都涉及某種類型的聚合,因此你可以忽略GROUP BY語句。)PIVOT運算符讓我們能夠利用CASE語句查詢實現(xiàn)相同的功能,但是你可以用更少的代碼就實現(xiàn),而且看起來更漂亮。

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é)果如下:


二、通過下面一個實例詳細介紹PIVOT的過程

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

三.UNPIVOT

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

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

UNPIVOT實例

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實例的分析

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

您可能感興趣的文章:
  • mysql 行轉(zhuǎn)列和列轉(zhuǎn)行實例詳解
  • mssql 數(shù)據(jù)庫表行轉(zhuǎn)列,列轉(zhuǎn)行終極方案
  • SQL行轉(zhuǎn)列和列轉(zhuǎn)行代碼詳解
  • sql語句實現(xiàn)行轉(zhuǎn)列的3種方法實例
  • SQLServer行轉(zhuǎn)列實現(xiàn)思路記錄
  • 一個簡單的SQL 行列轉(zhuǎn)換語句
  • MySQL存儲過程中使用動態(tài)行轉(zhuǎn)列
  • 數(shù)據(jù)庫實現(xiàn)行列轉(zhuǎn)換(mysql示例)
  • mysql 列轉(zhuǎn)行,合并字段的方法(必看)
  • SQL行轉(zhuǎn)列、列轉(zhuǎn)行的簡單實現(xiàn)

標簽:玉溪 文山 濟源 商洛 撫順 吉林 廣西 瀘州

巨人網(wǎng)絡(luò)通訊聲明:本文標題《深入SQL中PIVOT 行列轉(zhuǎn)換詳解》,本文關(guān)鍵詞  深入,SQL,中,PIVOT,行列,轉(zhuǎn)換,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題,煩請?zhí)峁┫嚓P(guān)信息告之我們,我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《深入SQL中PIVOT 行列轉(zhuǎn)換詳解》相關(guān)的同類信息!
  • 本頁收集關(guān)于深入SQL中PIVOT 行列轉(zhuǎn)換詳解的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章