http://www.studyofnet.com/news/295.html

本文導(dǎo)讀：T-SQL語句中，Pivot運(yùn)算符用于在列和行之間對數(shù)據(jù)進(jìn)行旋轉(zhuǎn)或透視轉(zhuǎn)換，PIVOT命令可以實(shí)現(xiàn)數(shù)據(jù)表的列轉(zhuǎn)行，同時(shí)執(zhí)行聚合運(yùn)算，UNPIVOT則與其相反，實(shí)現(xiàn)數(shù)據(jù)的行轉(zhuǎn)列。

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

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

?

一、PIVOT實(shí)例

1. 建表

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

SQL 代碼 ??復(fù)制 CREATE TABLE SalesByQuarter ( year INT, -- 年份 quarter CHAR(2), -- 季度 amount MONEY -- 總額 )

?

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

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

SQL 代碼 ??復(fù)制 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 END

?

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

?

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

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

SQL 代碼 ??復(fù)制 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語句和GROUP BY語句了。（每個(gè)PIVOT查詢都涉及某種類型的聚合，因此你可以忽略GROUP BY語句。）PIVOT運(yùn)算符讓我們能夠利用CASE語句查詢實(shí)現(xiàn)相同的功能，但是你可以用更少的代碼就實(shí)現(xiàn)，而且看起來更漂亮。
?

SQL 代碼 ??復(fù)制 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é)果如下：
?

?

?

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

?

SQL 代碼 ??復(fù)制 SELECT [星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日]--這里是PIVOT第三步（選擇行轉(zhuǎn)列后的結(jié)果集的列）這里可以用“*”表示選擇所有列，也可以只選擇某些列(也就是某些天) FROM WEEK_INCOME --這里是PIVOT第二步驟(準(zhǔn)備原始的查詢結(jié)果，因?yàn)镻IVOT是對一個(gè)原始的查詢結(jié)果集進(jìn)行轉(zhuǎn)換操作，所以先查詢一個(gè)結(jié)果集出來)這里可以是一個(gè)select子查詢，但為子查詢時(shí)候要指定別名，否則語法錯(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]就是說將week列的值分別轉(zhuǎn)換成一個(gè)個(gè)列，也就是“以值變列”。但是需要轉(zhuǎn)換成列的值有可能有很多，我們只想取其中幾個(gè)值轉(zhuǎn)換成列，那么怎樣取呢？就是在in里面了，比如我此刻只想看工作日的收入，在in里面就只寫“星期一”至“星期五”（注意，in里面是原來week列的值,"以值變列"）。總的來說，SUM(INCOME) for [week] in([星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日])這句的意思如果直譯出來，就是說：將列[week]值為"星期一","星期二","星期三","星期四","星期五","星期六","星期日"分別轉(zhuǎn)換成列，這些列的值取income的總和。 )TBL--別名一定要寫

?

三.UNPIVOT 很明顯，UN這個(gè)前綴表明了，它做的操作是跟PIVOT相反的，即列轉(zhuǎn)行。UNPIVOT操作涉及到以下三個(gè)邏輯處理階段。 1，生成副本 2，提取元素 3，刪除帶有NULL的行 UNPIVOT實(shí)例 ? SQL 代碼 ??復(fù)制 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, Emp5FROM 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)的每一列，都會生成一個(gè)副本。因?yàn)檫@里的IN子句有5個(gè)列名稱，所以要為每個(gè)來源行生成5個(gè)副本。結(jié)果得到的虛擬表中將新增一個(gè)列，用來以字符串格式保存來源列的名稱（for和IN之間的，上面例子是 Employee?）。第二步，根據(jù)新增的那一列中的值從來源列中提取出與列名對應(yīng)的行。第三步，刪除掉結(jié)果列值為null的行，完成這個(gè)查詢。

轉(zhuǎn)載于:https://www.cnblogs.com/liuqiyun/p/7600253.html

總結(jié)

以上是生活随笔為你收集整理的sql privot的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。