Oracle使用pivot和unpivot函數(shù)實現(xiàn)行列轉(zhuǎn)換

更新時間：2023年06月15日 09:20:13 作者：牛奶咖啡13

項目開發(fā)過程中常常會涉及到oracle數(shù)據(jù)庫的一個數(shù)據(jù)操作，那就是行列的互轉(zhuǎn)，本文為大家介紹了兩個可以實現(xiàn)這一操作的函數(shù)pivot和unpivot，感興趣的可以了解一下

2.1、實現(xiàn)將上圖內(nèi)容轉(zhuǎn)為行——decode函數(shù)
2.2、實現(xiàn)將上圖內(nèi)容轉(zhuǎn)為行——case when函數(shù)
2.3、實現(xiàn)將上圖內(nèi)容轉(zhuǎn)為行——pivot函數(shù)
2.4、實現(xiàn)將上圖內(nèi)容轉(zhuǎn)為列——unpivot函數(shù)

一、需求說明

項目開發(fā)過程中涉及到oracle數(shù)據(jù)庫的數(shù)據(jù)操作；但是需要將數(shù)據(jù)進行列的互轉(zhuǎn)，通過查閱資料可知在oracle中有三種方式可以實現(xiàn)行列互轉(zhuǎn)：

①使用decode 函數(shù)；

②使用case when 函數(shù)；

③使用pivot函數(shù)；

參考：Oracle中實現(xiàn)行列互轉(zhuǎn)的方法分享

二、實現(xiàn)方法

我這里有一個數(shù)據(jù)表內(nèi)容如下：

2.1、實現(xiàn)將上圖內(nèi)容轉(zhuǎn)為行——decode函數(shù)

select
  "name",
  max(decode("course", '語文', "score")) 語文,
  max(decode("course", '數(shù)學', "score")) 數(shù)學,
  max(decode("course", '英語', "score")) 英語,
  sum("score") 總分
from "grade"
group by "name";

2.2、實現(xiàn)將上圖內(nèi)容轉(zhuǎn)為行——case when函數(shù)

select
  "name",
  max(case when "course"  = '語文' then "score" end) 語文,
  max(case when "course" = '數(shù)學' then "score" end) 數(shù)學,
  max(case when "course" = '英語' then "score" end) 英語,
  sum("score") 總分
from "grade"
  group by "name";

2.3、實現(xiàn)將上圖內(nèi)容轉(zhuǎn)為行——pivot函數(shù)

pivot函數(shù)的語法：

pivot(聚合函數(shù) for 列名 in(類型))

select t.* from(
(select * from 原表名稱)
pivot(
max(需轉(zhuǎn)的列名稱)
for 需轉(zhuǎn)的列名稱 in(需轉(zhuǎn)列對應的值1,需轉(zhuǎn)列對應的值2,需轉(zhuǎn)列對應的值3
)
)t

SELECT t.*,(t.語文+t.數(shù)學+t.英語)總分 from ((SELECT "name","course","score" from "grade")pivot
(
	 max("score") 
	 for "course" in('語文' 語文,'數(shù)學' 數(shù)學,'英語' 英語)
))t ORDER BY "name";

2.4、實現(xiàn)將上圖內(nèi)容轉(zhuǎn)為列——unpivot函數(shù)

需要轉(zhuǎn)的內(nèi)容如下圖：

unpivot函數(shù)的語法：

SELECT 列名稱,需定義的列1名稱,需定義的列2名稱 from 表名稱 unpivot (需定義的列2名稱 for 需定義的列1名稱 in(列2值1,列2值2,列2值3));

SELECT "name" 名字,course 課程,score 分數(shù) from "grade2" unpivot (score for course in("chinese","math","english"));

SELECT 名字,course 課程,score 分數(shù) from (
SELECT "name" 名字,"chinese" 語文,"math" 數(shù)學,"english" 英語 from "grade2") unpivot (score for course in(語文,數(shù)學,英語))