1.测试数据

DROP TABLE TEST;
CREATE TABLE TEST(NAME VARCHAR(10),COURSE VARCHAR(10),SCORE INT);
INSERT INTO TEST VALUES('Amy','Chinese',85);
INSERT INTO TEST VALUES('Amy','Maths',74);
INSERT INTO TEST VALUES('Amy','English',98);
INSERT INTO TEST VALUES('Owen','Chinese',85);
INSERT INTO TEST VALUES('Owen','Maths',85);
INSERT INTO TEST VALUES('Owen','English',85);
COMMIT;

2.实现方法一：PIVOT函数

SELECT *
  FROM TEST
PIVOT(SUM(SCORE)
   FOR COURSE IN('Chinese' AS 语文, 'Maths' AS 数学, 'English' AS 英语));

3.实现方法二：DECODE函数

注意：还可以使用MAX DECODE,两个函数执行效率可以比较一下

SELECT *
  FROM (SELECT T.NAME AS 姓名,
               SUM(DECODE(T.COURSE, 'Chinese', T.SCORE, 0)) AS "Chinese",
               SUM(DECODE(T.COURSE, 'Maths', T.SCORE, 0)) AS "Maths",
               SUM(DECODE(T.COURSE, 'English', T.SCORE, 0)) AS "English"
          FROM TEST T
         GROUP BY T.NAME);

4.实现方法三：case

select NAME as 姓名,
       SUM(CASE COURSE
             when 'Chinese' then
              SCORE
             else
              0
           END) as 语文,
       SUM(CASE COURSE
             when 'Maths' then
              SCORE
             else
              0
           END) as 数学,
       SUM(CASE COURSE
             when 'English' then
              SCORE
             else
              0
           END) as 英语
  From TEST
 group by NAME;