目錄1、準(zhǔn)備數(shù)據(jù)：REST表2、查詢(xún)數(shù)據(jù)3、行轉(zhuǎn)列方式1：使用 case when then方式方式2： 使用 decode函數(shù)方式3：使用pivot函數(shù)4、列轉(zhuǎn)行5、直接使用unpivot函數(shù) --列轉(zhuǎn)行總結(jié) 1、準(zhǔn)備數(shù)據(jù)：REST表-- 創(chuàng)建表RESTCREATE TABLE REST ( 'ID' NUMBER, 'AMOUNT' NUMBER(19,0), 'MONTH' VARCHAR2(255 BYTE));--執(zhí)行添加數(shù)據(jù)語(yǔ)句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、查詢(xún)數(shù)據(jù)

3、行轉(zhuǎn)列方式1：使用 case when then方式

case 條件

when 值1 then 返回值1

when 值2 then 返回值2

..........

else 默認(rèn)值

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 默認(rèn)值

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

結(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ù)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

結(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ù)為空沒(méi)有賦值為0

下面這個(gè)方法解決null 轉(zhuǎn)為0 問(wèn)題

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

結(jié)果和方式1一樣：

4、列轉(zhuǎn)行

在上述pivot 方法的原sql語(yǔ)句上再加上unpivot函數(shù)，將列再轉(zhuǎn)為行，在unpivot函數(shù)中，amount：表示由列轉(zhuǎn)換為行后的數(shù)據(jù)

month：表示由列轉(zhuǎn)換為行后的列名

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

結(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');

查詢(xún)出的數(shù)據(jù)

列轉(zhuǎn)行sql

SELECT* FROM TESTunpivot ( 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)搜索好吧啦網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持好吧啦網(wǎng)！