oracle行转列与列转行的几种方式汇总

目录

• 1、准备数据：REST表
• 2、查询数据
• 3、行转列
• 方式1：使用 case when then方式
• 方式2： 使用 decode函数
• 方式3：使用pivot函数
• 4、列转行
• 5、直接使用unpivot函数 --列转行
• 总结

-- 创建表REST
CREATE 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');

-- 使用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 

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 

--	使用decode函数
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 

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

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

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

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

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