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)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: