oracle行轉(zhuǎn)列與列轉(zhuǎn)行的幾種方式匯總
更新時間：2023年05月29日 09:12:30 作者：SUMMERENT
最近項目需要進行行轉(zhuǎn)列,經(jīng)過上網(wǎng)查找到了一些解決方法,分享給大家,這篇文章主要給大家介紹了關(guān)于oracle行轉(zhuǎn)列與列轉(zhuǎn)行的幾種方式,文中通過圖文以及實例代碼介紹的非常詳細,需要的朋友可以參考下
1、準備數(shù)據(jù)：REST表
-- 創(chuàng)建表REST
CREATE TABLE REST (
"ID" NUMBER,
"AMOUNT" NUMBER(19,0),
"MONTH" VARCHAR2(255 BYTE)
);
--執(zhí)行添加數(shù)據(jù)語句
INSERT INTO "CERPAWCSADM"."REST" VALUES ('1', '100', 'Jan');
INSERT INTO "CERPAWCSADM"."REST" VALUES ('1', '100', 'Feb');
INSERT INTO "CERPAWCSADM"."REST" VALUES ('1', '66', 'Mar');
INSERT INTO "CERPAWCSADM"."REST" VALUES ('1', '77', 'Jun');
INSERT INTO "CERPAWCSADM"."REST" VALUES ('1', '88', 'Dec');
INSERT INTO "CERPAWCSADM"."REST" VALUES ('1', '12', 'Aug');
INSERT INTO "CERPAWCSADM"."REST" VALUES ('2', '22', 'Feb');
INSERT INTO "CERPAWCSADM"."REST" VALUES ('2', '33', 'Apr');
INSERT INTO "CERPAWCSADM"."REST" VALUES ('2', '232', 'Jul');
INSERT INTO "CERPAWCSADM"."REST" VALUES ('2', '43', 'Sep');
INSERT INTO "CERPAWCSADM"."REST" VALUES ('2', '544', 'Oct');
INSERT INTO "CERPAWCSADM"."REST" VALUES ('2', '65', 'Nov');2、查詢數(shù)據(jù)
3、行轉(zhuǎn)列
方式1：使用 case when then方式
case 條件
when 值1 then 返回值1
when 值2 then 返回值2
..........
else 默認值
end
-- 使用case when 方式 SELECT id, sum( CASE month WHEN 'Jan' THEN amount ELSE 0 END ) AS Jan_amount, sum( CASE month WHEN 'Feb' THEN amount ELSE 0 END ) AS Feb_amount, sum( CASE month WHEN 'Mar' THEN amount ELSE 0 END ) AS Mar_amount, sum( CASE month WHEN 'Apr' THEN amount ELSE 0 END ) AS Apr_amount, sum( CASE month WHEN 'May' THEN amount ELSE 0 END ) AS May_amount, sum( CASE month WHEN 'Jun' THEN amount ELSE 0 END ) AS Jun_amount, sum( CASE month WHEN 'Jul' THEN amount ELSE 0 END ) AS Jul_amount, sum( CASE month WHEN 'Aug' THEN amount ELSE 0 END ) AS Aug_amount, sum( CASE month WHEN 'Sep' THEN amount ELSE 0 END ) AS Sep_amount, sum( CASE month WHEN 'Oct' THEN amount ELSE 0 END ) AS Oct_amount, sum( CASE month WHEN 'Nov' THEN amount ELSE 0 END ) AS Nov_amount, sum( CASE month WHEN 'Dec' THEN amount ELSE 0 END ) AS Dec_amount FROM REST GROUP BY id
case when 另一種方式：
case when 條件 = 值1 then 返回值1
case when 條件 = 值1 then 返回值1
else 默認值
end
SELECT id, sum( CASE WHEN month ='Jan' THEN amount ELSE 0 END ) AS Jan_amount, sum( CASE WHEN month = 'Feb' THEN amount ELSE 0 END ) AS Feb_amount, sum( CASE WHEN month = 'Mar' THEN amount ELSE 0 END ) AS Mar_amount, sum( CASE WHEN month = 'Apr' THEN amount ELSE 0 END ) AS Apr_amount, sum( CASE WHEN month = 'May' THEN amount ELSE 0 END ) AS May_amount, sum( CASE WHEN month = 'Jun' THEN amount ELSE 0 END ) AS Jun_amount, sum( CASE WHEN month = 'Jul' THEN amount ELSE 0 END ) AS Jul_amount, sum( CASE WHEN month = 'Aug' THEN amount ELSE 0 END ) AS Aug_amount, sum( CASE WHEN month = 'Sep' THEN amount ELSE 0 END ) AS Sep_amount, sum( CASE WHEN month = 'Oct' THEN amount ELSE 0 END ) AS Oct_amount, sum( CASE WHEN month = 'Nov' THEN amount ELSE 0 END ) AS Nov_amount, sum( CASE WHEN month = 'Dec' THEN amount ELSE 0 END ) AS Dec_amount FROM REST GROUP BY id
結(jié)果為：
方式2： 使用 decode函數(shù)
decode函數(shù)： DECODE(條件, 值1, 返回值1, 值2,返回值2, 值3,返回值3, . . . else 缺省值)
含義：if 條件 = 值1 then 返回值1 elsif 條件 = 值2 then 返回值2 else (缺省值) endif
-- 使用decode函數(shù) SELECT id, sum( decode( month, 'Jan', amount, 0 ) ) Jan_amount, sum( decode( month, 'Feb', amount, 0 ) ) Feb_amount, sum( decode( month, 'Mar', amount, 0 ) ) Mar_amount, sum( decode( month, 'Apr', amount, 0 ) ) Apr_amount, sum( decode( month, 'May', amount, 0 ) ) May_amount, sum( decode( month, 'Jun', amount, 0 ) ) Jun_amount, sum( decode( month, 'Jul', amount, 0 ) ) Jul_amount, sum( decode( month, 'Aug', amount, 0 ) ) Aug_amount, sum( decode( month, 'Sep', amount, 0 ) ) Sep_amount, sum( decode( month, 'Oct', amount, 0 ) ) Oct_amount, sum( decode( month, 'Nov', amount, 0 ) ) Nov_amount, sum( decode( month, 'Dec', amount, 0 ) ) Dec_amount FROM REST GROUP BY id
結(jié)果和方式1一樣
方式3：使用pivot函數(shù)
pivot
(
<聚合函數(shù)>(要聚合的列)
for <要轉(zhuǎn)換的列> in (要轉(zhuǎn)換的列值 as 要轉(zhuǎn)換成的列名)
)
SELECT * FROM REST pivot ( SUM(amount) FOR month IN ( 'Jan' AS Jan_amount, 'Feb' AS Feb_amount, 'Mar' AS Mar_amount, 'Apr' AS Apr_amount, 'May' AS May_amount, 'Jun' AS Jun_amount, 'Jul' AS Jul_amount, 'Aug' AS Aug_amount, 'Sep' AS Sep_amount, 'Oct' AS Oct_amount, 'Nov' AS Nov_amount, 'Dec' AS Dec_amount ) );
結(jié)果為：這個結(jié)果會發(fā)現(xiàn)，如果數(shù)據(jù)為空沒有賦值為0
下面這個方法解決null 轉(zhuǎn)為0 問題
SELECT NVl(Jan_amount,0) Jan_amount, NVl(Feb_amount,0) Feb_amount, NVl(Mar_amount,0) Mar_amount, NVl(Apr_amount,0) Apr_amount, NVl(May_amount,0) May_amount, NVl(Jun_amount,0) Jun_amount, NVl(Jul_amount,0) Jul_amount, NVl(Aug_amount,0) Aug_amount, NVl(Sep_amount,0) Sep_amount, NVl(Oct_amount,0) Oct_amount, NVl(Nov_amount,0) Nov_amount, NVl(Dec_amount,0) Dec_amount FROM REST pivot ( SUM(amount) FOR month IN ( 'Jan' AS Jan_amount, 'Feb' AS Feb_amount, 'Mar' AS Mar_amount, 'Apr' AS Apr_amount, 'May' AS May_amount, 'Jun' AS Jun_amount, 'Jul' AS Jul_amount, 'Aug' AS Aug_amount, 'Sep' AS Sep_amount, 'Oct' AS Oct_amount, 'Nov' AS Nov_amount, 'Dec' AS Dec_amount ) );
結(jié)果和方式1一樣：
4、列轉(zhuǎn)行
在上述pivot 方法的原sql語句上再加上unpivot函數(shù)，將列再轉(zhuǎn)為行，在unpivot函數(shù)中，amount：表示由列轉(zhuǎn)換為行后的數(shù)據(jù)
month：表示由列轉(zhuǎn)換為行后的列名
select * from REST pivot (sum(amount) for month in ( 'Jan' AS Jan_amount, 'Feb' AS Feb_amount, 'Mar' AS Mar_amount, 'Apr' AS Apr_amount, 'May' AS May_amount, 'Jun' AS Jun_amount, 'Jul' AS Jul_amount, 'Aug' AS Aug_amount, 'Sep' AS Sep_amount, 'Oct' AS Oct_amount, 'Nov' AS Nov_amount, 'Dec' AS Dec_amount )) unpivot ( amount for month in(Jan_amount,Feb_amount,Mar_amount,Apr_amount,May_amount,Jun_amount,Jul_amount,Aug_amount,Sep_amount,Oct_amount,Nov_amount,Dec_amount));
結(jié)果為：
5、直接使用unpivot函數(shù) --列轉(zhuǎn)行
準備數(shù)據(jù)：TEST表
CREATE TABLE TEST (
"ID" NUMBER(12,0) NOT NULL,
"JAN" VARCHAR2(255 BYTE),
"FEB" VARCHAR2(255 BYTE),
"MAR" VARCHAR2(255 BYTE),
"APR" VARCHAR2(255 BYTE),
"MAY" VARCHAR2(255 BYTE),
"JUN" VARCHAR2(255 BYTE),
"JUL" VARCHAR2(255 BYTE),
"AUG" VARCHAR2(255 BYTE),
"SEP" VARCHAR2(255 BYTE),
"OCT" VARCHAR2(255 BYTE),
"NOV" VARCHAR2(255 BYTE),
"DEC" VARCHAR2(255 BYTE)
);
-- 插入數(shù)據(jù)
INSERT INTO "CERPAWCSADM"."TEST" VALUES ('1', '33', '2', '3', '4', '5', '6', '7', '8', '9', '99', '8', '6');
INSERT INTO "CERPAWCSADM"."TEST" VALUES ('2', '22', '3', '4', '6', '5', '7', '0', '7', '22', '21', '343', '76');
INSERT INTO "CERPAWCSADM"."TEST" VALUES ('3', '88', '3', '4', '5', '7', '9', '7', '2', '2', '231', '56', '78');查詢出的數(shù)據(jù)
列轉(zhuǎn)行sql
SELECT * FROM TEST unpivot ( amount for month in(JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC));
結(jié)果為：
總結(jié)
到此這篇關(guān)于oracle行轉(zhuǎn)列與列轉(zhuǎn)行的幾種方式匯總的文章就介紹到這了,更多相關(guān)oracle行轉(zhuǎn)列與列轉(zhuǎn)行內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
Oracle的數(shù)據(jù)字典技術(shù)簡析...2007-03-03
這篇文章主要介紹了Linux下的Oracle啟動腳本及其開機自啟動的方法，大家可以參考使用2013-11-11
淺談oracle rac和分布式數(shù)據(jù)庫的區(qū)別
這篇文章主要介紹了oracle rac和分布式數(shù)據(jù)庫的區(qū)別的相關(guān)內(nèi)容，小編覺得挺不錯的，這里給大家分享下，需要的朋友可以參考。2017-10-10
這篇文章主要介紹了Oracle體系結(jié)構(gòu)的相關(guān)知識，本文給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值,需要的朋友可以參考下2021-01-01
這篇文章主要介紹了oracle 身份證校驗函數(shù)，本文通過實例代碼給大家介紹的非常詳細，具有一定的參考借鑒價值,需要的朋友可以參考下2020-01-01
oracle數(shù)據(jù)庫中chr()函數(shù)和concat函數(shù)的使用說明
這篇文章主要介紹了oracle數(shù)據(jù)庫中chr()函數(shù)和concat函數(shù)的使用說明,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-07-07
Oracle中使用觸發(fā)器(trigger)和序列(sequence)模擬實現(xiàn)自增列實例
這篇文章主要介紹了Oracle中使用觸發(fā)器(trigger)和序列(sequence)模擬實現(xiàn)自增列實例,包含代碼實例,而且小編已經(jīng)測試過了哦,需要的朋友可以參考下2014-07-07
在Oracle數(shù)據(jù)庫中同時更新兩張表的簡單方法
這篇文章主要介紹了在Oracle數(shù)據(jù)庫中同時更新兩張表的簡單方法,同時介紹了一種差異性合并更新的方法,需要的朋友可以參考下2015-11-11
使用Navicat連接Oracle數(shù)據(jù)庫時,您需要輸入正確的用戶名和密碼,如果您遺忘了密碼,或者需要為用戶設(shè)置新密碼,則需要通過Linux系統(tǒng)中的Oracle管理工具修改密碼,本文將為您提供詳細的步驟,需要的朋友可以參考下2024-12-12
Oracle?range時間范圍自動分區(qū)的創(chuàng)建方式
這篇文章主要介紹了Oracle??range時間范圍自動分區(qū)的創(chuàng)建方式,本文給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2023-04-04