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

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 kecheng
GROUP 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 kecheng
GROUP 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) WEEK7
FROM FORSRCMIDSCHEDULE
GROUP BY id 

原数据模式

转变后

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

SELECT ID,NAME,
wmsys.wm_concat(course || '-'||score) course
FROM kecheng
GROUP BY ID ,NAME;