ORACLE学习笔记--合并多行数据到一行中
CREATE TABLE GRADES( ID NUMBER PRIMARY KEY, NAME VARCHAR2(10) NOT NULL, COURSE VARCHAR2(10) NOT NULL, GRADE NUMBER NOT NULL);COMMIT;DELETE FROM GRADES;COMMIT;INSERT INTO GRADES(ID,NAME,COURSE,GRADE) VALUES(1,'周健','SX',78);INSERT INTO GRADES(ID,NAME,COURSE,GRADE) VALUES(2,'周健','YW',88);INSERT INTO GRADES(ID,NAME,COURSE,GRADE) VALUES(3,'周健','WL',42);INSERT INTO GRADES(ID,NAME,COURSE,GRADE) VALUES(4,'周健','HX',100);INSERT INTO GRADES(ID,NAME,COURSE,GRADE) VALUES(5,'张海威','SX',69);INSERT INTO GRADES(ID,NAME,COURSE,GRADE) VALUES(6,'张海威','YW',95);INSERT INTO GRADES(ID,NAME,COURSE,GRADE) VALUES(7,'张海威','WL',46);INSERT INTO GRADES(ID,NAME,COURSE,GRADE) VALUES(8,'张超','SX',31);INSERT INTO GRADES(ID,NAME,COURSE,GRADE) VALUES(9,'张超','YW',97);INSERT INTO GRADES(ID,NAME,COURSE,GRADE) VALUES(10,'张超','HX',89);COMMIT;
SELECT NAME 姓名,NVL(SUM(DECODE(COURSE,'SX',GRADE))||'','未修') 数学,NVL(SUM(DECODE(COURSE,'YW',GRADE))||'','未修') 语文,NVL(SUM(DECODE(COURSE,'WL',GRADE))||'','未修') 物理,NVL(SUM(DECODE(COURSE,'HX',GRADE))||'','未修') 化学FROM GRADESGROUP BY NAME;
SELECT NAME 姓名, NVL(SUM(SX)||'','未修') 数学, NVL(SUM(YW)||'','未修') 语文, NVL(SUM(WL)||'','未修') 物理, NVL(SUM(HX)||'','未修') 化学FROM ( SELECT NAME, DECODE(COURSE,'SX',GRADE) SX, DECODE(COURSE,'YW',GRADE) YW, DECODE(COURSE,'WL',GRADE) WL, DECODE(COURSE,'HX',GRADE) HX FROM GRADES)GROUP BY NAME;
?
