转载地址（本文所有代码都曾经运行过）

https://www.cnblogs.com/liunanjava/p/4961923.html

根底表及数据

 create table kecheng(  id     NUMBER,  name   VARCHAR2(20),  course VARCHAR2(20),  score  NUMBER);insert into kecheng (id, name, course, score)values (1, '张三', '语文', 67);insert into kecheng (id, name, course, score)values (1, '张三', '数学', 76);insert into kecheng (id, name, course, score)values (1, '张三', '英语', 43);insert into kecheng (id, name, course, score)values (1, '张三', '历史', 56);insert into kecheng (id, name, course, score)values (1, '张三', '化学', 11);insert into kecheng (id, name, course, score)values (2, '李四', '语文', 54);insert into kecheng (id, name, course, score)values (2, '李四', '数学', 81);insert into kecheng (id, name, course, score)values (2, '李四', '英语', 64);insert into kecheng (id, name, course, score)values (2, '李四', '历史', 93);insert into kecheng (id, name, course, score)values (2, '李四', '化学', 27);insert into kecheng (id, name, course, score)values (3, '王五', '语文', 24);insert into kecheng (id, name, course, score)values (3, '王五', '数学', 25);insert into kecheng (id, name, course, score)values (3, '王五', '英语', 8);insert into kecheng (id, name, course, score)values (3, '王五', '历史', 45);insert into kecheng (id, name, course, score)values (3, '王五', '化学', 1);commit;

固定行转换

应用decode实现行转列

SELECT ID,NAME,SUM(DECODE(course,'语文',score,0)) 语文,--这里应用max,min都能够SUM(DECODE(course,'数学',score,0)) 数学,SUM(DECODE(course,'英语',score,0)) 英语,SUM(DECODE(course,'历史',score,0)) 历史,SUM(DECODE(course,'化学',score,0)) 化学FROM kechengGROUP BY ID ,NAME

应用case实现行转换

SELECT ID,NAME,MAX(CASE WHEN course='语文' THEN score ELSE 0 END) 语文,MAX(CASE WHEN course='数学' THEN score ELSE 0 END) 数学,MAX(CASE WHEN course='英语' THEN score ELSE 0 END) 英语,MAX(CASE WHEN course='历史' THEN score ELSE 0 END) 历史,MAX(CASE WHEN course='化学' THEN score ELSE 0 END) 化学FROM kechengGROUP BY ID ,NAME

 SELECT id, MAX(CASE WHEN week=1  THEN COURSE_NAME ELSE '' END) WEEK1,MAX(CASE WHEN week=2  THEN COURSE_NAME ELSE '' END) WEEK2,MAX(CASE WHEN week=3  THEN COURSE_NAME ELSE '' END) WEEK3,MAX(CASE WHEN week=4  THEN COURSE_NAME ELSE '' END) WEEK4,MAX(CASE WHEN week=5  THEN COURSE_NAME ELSE '' END) WEEK5,MAX(CASE WHEN week=6  THEN COURSE_NAME ELSE '' END) WEEK6,MAX(CASE WHEN week=7  THEN COURSE_NAME ELSE '' END) WEEK7FROM FORSRCMIDSCHEDULEGROUP BY id 

原数据模式

转变后

wmsys.wm_concat实现行转换，并值是自定义形式

SELECT ID,NAME,wmsys.wm_concat(course || '-'||score) courseFROM kechengGROUP BY ID ,NAME;