Oracle 字段格式显示

 建表语句（ORACL）（含有记录）

-- CREATE TABLE
CREATE TABLE STUDENT
(
  STUDENTID     NUMBER(10),
  STUDENTNAME   VARCHAR2(30),
  STUDENTSCORES NUMBER(3)
)

INSERT INTO STUDENT (STUDENTID, STUDENTNAME, STUDENTSCORES)
VALUES (1001, '李四', 100);
INSERT INTO STUDENT (STUDENTID, STUDENTNAME, STUDENTSCORES)
VALUES (1002, '李四', 125);
INSERT INTO STUDENT (STUDENTID, STUDENTNAME, STUDENTSCORES)
VALUES (1003, '李四', 98);
INSERT INTO STUDENT (STUDENTID, STUDENTNAME, STUDENTSCORES)
VALUES (1001, '张三', 100);
INSERT INTO STUDENT (STUDENTID, STUDENTNAME, STUDENTSCORES)
VALUES (1002, '张三', 125);
INSERT INTO STUDENT (STUDENTID, STUDENTNAME, STUDENTSCORES)
VALUES (1003, '张三', 99);

原始显示

要求这么显示

 

注：其中，STUDENTID（1001、1002、1003）分别代表 语、数、外；STUDENTNAME是姓名；STUDENTSCORES是学分。

 

答案

SELECT S.STUDENTNAME AS "姓名" ,
       MAX(DECODE(S.STUDENTID,1001,S.STUDENTSCORES,0)) AS "语文",
       MAX(DECODE(S.STUDENTID,1002,S.STUDENTSCORES,0)) AS "数学",
       MAX(DECODE(S.STUDENTID,1003,S.STUDENTSCORES,0)) AS "英语"
FROM STUDENT S
GROUP BY S.STUDENTNAME