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 實作行轉列

;