oracle行轉列與列轉行的幾種方式匯總

目錄1、準備數據：REST表2、查詢數據3、行轉列方式1：使用 case when then方式方式2： 使用 decode函數方式3：使用pivot函數4、列轉行5、直接使用unpivot函數 --列轉行總結 1、準備數據：REST表-- 創建表RESTCREATE TABLE REST ( 'ID' NUMBER, 'AMOUNT' NUMBER(19,0), 'MONTH' VARCHAR2(255 BYTE));--執行添加數據語句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、查詢數據

3、行轉列方式1：使用 case when then方式

case 條件

when 值1 then 返回值1

when 值2 then 返回值2

..........

else 默認值

end

-- 使用case when 方式SELECTid,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 FROMREST GROUP BYid

case when 另一種方式：

case when 條件 = 值1 then 返回值1

case when 條件 = 值1 then 返回值1

else 默認值

end

SELECTid,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 FROMREST GROUP BYid

結果為：

方式2： 使用 decode函數

decode函數： DECODE(條件, 值1, 返回值1, 值2,返回值2, 值3,返回值3, . . . else 缺省值)

含義：if 條件 = 值1 then 返回值1 elsif 條件 = 值2 then 返回值2 else (缺省值) endif

--使用decode函數SELECTid,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 FROMREST GROUP BYid

結果和方式1一樣

方式3：使用pivot函數

pivot(<聚合函數>(要聚合的列)for <要轉換的列> in (要轉換的列值 as 要轉換成的列名))

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 ) );

結果為：這個結果會發現，如果數據為空沒有賦值為0

下面這個方法解決null 轉為0 問題

SELECTNVl(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_amountFROM 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 ) );

結果和方式1一樣：

4、列轉行

在上述pivot 方法的原sql語句上再加上unpivot函數，將列再轉為行，在unpivot函數中，amount：表示由列轉換為行后的數據

month：表示由列轉換為行后的列名

select * from RESTpivot (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));

結果為：

5、直接使用unpivot函數 --列轉行

準備數據：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));-- 插入數據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');

查詢出的數據

列轉行sql

SELECT* FROM TESTunpivot ( amount for month in(JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC));

結果為：

總結

到此這篇關于oracle行轉列與列轉行的幾種方式匯總的文章就介紹到這了,更多相關oracle行轉列與列轉行內容請搜索好吧啦網以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持好吧啦網！