oracle通过decode/case when实现行转列

表结构及数据准备：

-- ----------------------------
-- Table structure for STUDENT_SCORE
-- ----------------------------
DROP TABLE "STUDENT_SCORE";
CREATE TABLE "STUDENT_SCORE" (
  "NAME" VARCHAR2(20 BYTE) VISIBLE,
  "SUBJECT" VARCHAR2(20 BYTE) VISIBLE,
  "SCORE" NUMBER(4,1) VISIBLE
)
;

-- ----------------------------
-- Records of STUDENT_SCORE
-- ----------------------------
INSERT INTO "STUDENT_SCORE" VALUES ('张三', '语文', '11');
INSERT INTO "STUDENT_SCORE" VALUES ('张三', '数学', '22');
INSERT INTO "STUDENT_SCORE" VALUES ('张三', '英语', '33');
INSERT INTO "STUDENT_SCORE" VALUES ('李四', '语文', '44');
INSERT INTO "STUDENT_SCORE" VALUES ('李四', '数学', '55');
INSERT INTO "STUDENT_SCORE" VALUES ('李四', '英语', '66');
INSERT INTO "STUDENT_SCORE" VALUES ('王五', '语文', '77');
INSERT INTO "STUDENT_SCORE" VALUES ('王五', '数学', '88');
INSERT INTO "STUDENT_SCORE" VALUES ('王五', '英语', '99');
           

decode行转列：

SELECT
	name "姓名",
	sum( decode( subject, '语文', nvl( score, 0 ), 0 ) ) "语文",
	sum( decode( subject, '数学', nvl( score, 0 ), 0 ) ) "数学",
	sum( decode( subject, '英语', nvl( score, 0 ), 0 ) ) "英语" 
FROM
	student_score 
GROUP BY
	name;
 -- group之后可以显示group by的那些列或者通过函数显示
 
           

--decode(条件,值1,返回值1,值2,返回值2.....值n,返回值n,default)
 if 条件=值1 then
  	return 返回值1;
 elsif 条件=值2 then
  	return 返回值2;
 .....
 elsif 条件=值n then
  	renturn 返回值3;
 else
 	return default;
 end if
 --NVL(eExpression1, eExpression2)
 if eExpression1==NULL
 then
 	return eExpression1;
 else
 	return eExpression2;
 
 endif
           

case when 行转列

SELECT
	name "姓名",
	sum( CASE WHEN subject = '语文' THEN nvl( score, 0 ) ELSE 0 END ) "语文",
	sum( CASE WHEN subject = '数学' THEN nvl( score, 0 ) ELSE 0 END ) "数学",
	sum( CASE WHEN subject = '英语' THEN nvl( score, 0 ) ELSE 0 END ) "英语" 
FROM
	student_score 
GROUP BY
	name;
           

数据库图：

实现效果图：

参考链接：oracle使用decode函数或case when 实现行转列

;