深入SQL中PIVOT 行列轉(zhuǎn)換詳解
PIVOT通過將表達(dá)式某一列中的唯一值轉(zhuǎn)換為輸出中的多個(gè)列來旋轉(zhuǎn)表值表達(dá)式,并在必要時(shí)對(duì)最終輸出中所需的任何其余列值執(zhí)行聚合。UNPIVOT與PIVOT執(zhí)行相反的操作,將表值表達(dá)式的列轉(zhuǎn)換為列值。
通俗簡(jiǎn)單的說:PIVOT就是行轉(zhuǎn)列,UNPIVOT就是列傳行
一、PIVOT實(shí)例
1. 建表
建立一個(gè)銷售情況表,其中,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以前的版本里,將行級(jí)數(shù)據(jù)轉(zhuǎn)換為列級(jí)數(shù)據(jù)就要用到一系列CASE語句和聚合查詢。雖然這種方式讓開發(fā)人員具有了對(duì)所返回?cái)?shù)據(jù)進(jì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語句和GROUP BY語句了。(每個(gè)PIVOT查詢都涉及某種類型的聚合,因此你可以忽略GROUP BY語句。)PIVOT運(yùn)算符讓我們能夠利用CASE語句查詢實(shí)現(xiàn)相同的功能,但是你可以用更少的代碼就實(shí)現(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é)果如下:
二、通過下面一個(gè)實(shí)例詳細(xì)介紹PIVOT的過程
SELECT [星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日]--這里是PIVOT第三步(選擇行轉(zhuǎn)列后的結(jié)果集的列)這里可以用“*”表示選擇所有列,也可以只選擇某些列(也就是某些天) FROM WEEK_INCOME --這里是PIVOT第二步驟(準(zhǔn)備原始的查詢結(jié)果,因?yàn)镻IVOT是對(duì)一個(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í)例
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è)列名稱,所以要為每個(gè)來源行生成5個(gè)副本。結(jié)果得到的虛擬表中將新增一個(gè)列,用來以字符串格式保存來源列的名稱(for和IN之間的,上面例子是 Employee )。第二步,根據(jù)新增的那一列中的值從來源列中提取出與列名對(duì)應(yīng)的行。第三步,刪除掉結(jié)果列值為null的行,完成這個(gè)查詢。
相關(guān)文章
MSSQL轉(zhuǎn)MySQL數(shù)據(jù)庫的實(shí)際操作記錄
今天把一個(gè)MSSQL的數(shù)據(jù)庫轉(zhuǎn)成MySQL,在沒有轉(zhuǎn)換工具的情況下,對(duì)于字段不多的數(shù)據(jù)表我用了如下手功轉(zhuǎn)換的方法,還算方便。MSSQL使用企業(yè)管理器操作,MySQL用phpmyadmin操作。2010-06-06Navicat?Premium自定義?sql?標(biāo)簽的創(chuàng)建方式
Navicat 中可以自定義一下sql語句的標(biāo)簽,方便開發(fā)者使用,這篇文章主要介紹了Navicat?Premium自定義sql標(biāo)簽的創(chuàng)建方式,包括自定義標(biāo)簽創(chuàng)建方式,結(jié)合示例代碼給大家介紹的非常詳細(xì),需要的朋友可以參考下2022-09-09windows環(huán)境下python連接openGauss數(shù)據(jù)庫的全過程
openGauss是一款全面友好開放,攜手伙伴共同打造的企業(yè)級(jí)開源關(guān)系型數(shù)據(jù)庫,這篇文章主要給大家介紹了關(guān)于windows環(huán)境下python連接openGauss數(shù)據(jù)庫的相關(guān)資料,文中通過實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下2023-01-01Linux下實(shí)現(xiàn)OpenGauss數(shù)據(jù)庫遠(yuǎn)程連接的教程
openGauss是一款開源關(guān)系型數(shù)據(jù)庫管理系統(tǒng),采用木蘭寬松許可證v2發(fā)行,本文主要為大家詳細(xì)介紹了如何在Linux環(huán)境下實(shí)現(xiàn)OpenGauss數(shù)據(jù)庫遠(yuǎn)程連接,需要的可以參考下2023-09-09dbeaver批量導(dǎo)出數(shù)據(jù)到另一個(gè)數(shù)據(jù)庫的詳細(xì)圖文教程
DBeaver是一款數(shù)據(jù)庫管理軟件,小巧易用,最主要其官方版就可以滿足平常得任務(wù)需求,這篇文章主要給大家介紹了關(guān)于dbeaver批量導(dǎo)出數(shù)據(jù)到另一個(gè)數(shù)據(jù)庫的相關(guān)資料,文中通過圖文介紹的非常詳細(xì),需要的朋友可以參考下2024-03-03SQL查詢出表、存儲(chǔ)過程、觸發(fā)器的創(chuàng)建時(shí)間和最后修改時(shí)間示例
這篇文章主要介紹了SQL查詢出表、存儲(chǔ)過程、觸發(fā)器的創(chuàng)建時(shí)間和最后修改時(shí)間示例,本文直接給出代碼實(shí)例,需要的朋友可以參考下2015-06-06