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