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)文章
這篇文章主要給大家介紹了關(guān)于Oracle往某表批量插入記錄的幾種實現(xiàn)方法,Oracle批量插入語句與其他數(shù)據(jù)庫不同,文中通過代碼實例介紹的非常詳細,需要的朋友可以參考下2023-07-07
安裝Oracle完整客戶端后沒有訪問接口OraOLEDB.Oracle解決辦法
這篇文章主要給大家介紹了關(guān)于安裝Oracle完整客戶端后沒有訪問接口OraOLEDB.Oracle的解決辦法,文中通過代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考解決價值,需要的朋友可以參考下2024-01-01
直接拷貝數(shù)據(jù)文件實現(xiàn)Oracle數(shù)據(jù)遷移
Oracle 數(shù)據(jù)遷移是比較麻煩的，對菜鳥來說更是如此。最近由于更換服務器，需要將Oracle遷移到另外一臺機器，在兩個服務器環(huán)境相同，以及 Oracle版本相同的前提下，通過直接拷貝數(shù)據(jù)文件到新服務器，就可以直接遷移成功。這里記錄一下遷移步驟。需要的朋友可以參考。2017-01-01
oracle sys_connect_by_path 函數(shù) 結(jié)果集連接
這幾天和幾個網(wǎng)上朋友一起探討oracle開發(fā)中的一些特別之處，談到了豎橫對換的方式。2009-07-07
oracle的體系太龐大了，對于初學者來說，難免會有些無從下手的感覺，什么都想學，結(jié)果什么都學不好，所以把學習經(jīng)驗共享一下，希望讓剛剛?cè)腴T的人對oracle有一個總體的認識，少走一些彎路。2009-05-05
oracle中dblink查看、創(chuàng)建、使用以及刪除實例代碼
當用戶要跨本地數(shù)據(jù)庫訪問另外一個數(shù)據(jù)庫表中的數(shù)據(jù)時,本地數(shù)據(jù)庫中必須創(chuàng)建了遠程數(shù)據(jù)庫的DBLINK,下面這篇文章主要給大家介紹了關(guān)于oracle中dblink查看、創(chuàng)建、使用以及刪除的相關(guān)資料,需要的朋友可以參考下2022-04-04
這篇文章主要介紹了Oracle實現(xiàn)動態(tài)SQL的拼裝要領,對于Oracle的進一步學習來說非常重要,需要的朋友可以參考下2014-07-07
這篇文章主要介紹了Oracle Procedure 知識，包括oracle的存儲過程注意事項方面的內(nèi)容,非常不錯，具有參考借鑒價值，需要的朋友可以參考下2017-02-02
解決Oracle安裝遇到Enterprise Manager配置失敗問題
這篇文章主要介紹了Oracle安裝遇到Enterprise Manager配置失敗問題,本文給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2020-12-12
這篇文章主要介紹了oracle中merge into用法及實例解析，小編覺得挺不錯的，現(xiàn)在分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2018-03-03