SQL查询结果行转列

练习脚本

    -- 学生表
    CREATE TABLE student (
      stuid VARCHAR(16) NOT NULL,
      stunm VARCHAR(20) NOT NULL,
      PRIMARY KEY (stuid)
    );
     
    -- 课程表
    CREATE TABLE courses (
      courseno VARCHAR(20) NOT NULL,
      coursenm VARCHAR(100) NOT NULL,
      PRIMARY KEY (courseno)
    );
     
    -- 成绩表
    CREATE TABLE score (
      stuid VARCHAR(16) NOT NULL,
      courseno VARCHAR(20) NOT NULL,
      scores FLOAT NULL DEFAULT NULL,
      PRIMARY KEY (stuid, courseno)
    );
     
    -- 插入数据
     
    -- 学生表数据
    Insert Into student (stuid, stunm) Values('1001', '张三');
    Insert Into student (stuid, stunm) Values('1002', '李四');
    Insert Into student (stuid, stunm) Values('1003', '赵二');
    Insert Into student (stuid, stunm) Values('1004', '王五');
    Insert Into student (stuid, stunm) Values('1005', '刘青');
    Insert Into student (stuid, stunm) Values('1006', '周明');
     
    -- 课程表数据
    Insert Into courses (courseno, coursenm) Values('C001', '大学语文');
    Insert Into courses (courseno, coursenm) Values('C002', '新视野英语');
    Insert Into courses (courseno, coursenm) Values('C003', '离散数学');
    Insert Into courses (courseno, coursenm) Values('C004', '概率论与数理统计');
    Insert Into courses (courseno, coursenm) Values('C005', '线性代数');
    Insert Into courses (courseno, coursenm) Values('C006', '高等数学(一)');
    Insert Into courses (courseno, coursenm) Values('C007', '高等数学(二)');
     
    -- 成绩表数据
    Insert Into score(stuid, courseno, scores) Values('1001', 'C001', 67);
    Insert Into score(stuid, courseno, scores) Values('1002', 'C001', 68);
    Insert Into score(stuid, courseno, scores) Values('1003', 'C001', 69);
    Insert Into score(stuid, courseno, scores) Values('1004', 'C001', 70);
    Insert Into score(stuid, courseno, scores) Values('1005', 'C001', 71);
    Insert Into score(stuid, courseno, scores) Values('1006', 'C001', 72);
    Insert Into score(stuid, courseno, scores) Values('1001', 'C002', 87);
    Insert Into score(stuid, courseno, scores) Values('1002', 'C002', 88);
    Insert Into score(stuid, courseno, scores) Values('1003', 'C002', 89);
    Insert Into score(stuid, courseno, scores) Values('1004', 'C002', 90);
    Insert Into score(stuid, courseno, scores) Values('1005', 'C002', 91);
    Insert Into score(stuid, courseno, scores) Values('1006', 'C002', 92);
    Insert Into score(stuid, courseno, scores) Values('1001', 'C003', 83);
    Insert Into score(stuid, courseno, scores) Values('1002', 'C003', 84);
    Insert Into score(stuid, courseno, scores) Values('1003', 'C003', 85);
    Insert Into score(stuid, courseno, scores) Values('1004', 'C003', 86);
    Insert Into score(stuid, courseno, scores) Values('1005', 'C003', 87);
    Insert Into score(stuid, courseno, scores) Values('1006', 'C003', 88);
    Insert Into score(stuid, courseno, scores) Values('1001', 'C004', 88);
    Insert Into score(stuid, courseno, scores) Values('1002', 'C004', 89);
    Insert Into score(stuid, courseno, scores) Values('1003', 'C004', 90);
    Insert Into score(stuid, courseno, scores) Values('1004', 'C004', 91);
    Insert Into score(stuid, courseno, scores) Values('1005', 'C004', 92);
    Insert Into score(stuid, courseno, scores) Values('1006', 'C004', 93);
    Insert Into score(stuid, courseno, scores) Values('1001', 'C005', 77);
    Insert Into score(stuid, courseno, scores) Values('1002', 'C005', 78);
    Insert Into score(stuid, courseno, scores) Values('1003', 'C005', 79);
     

题目：查询每个学生没门课程的成绩

    -- 查询数据
    select st.stuid, st.stunm from student st
     
    select sc.stuid, sc.courseno, sc.scores from score sc
     
    select cs.courseno, cs.coursenm from courses cs
     
    -- 查询每个学生没门课程的成绩
    -- 方式一；拆分选择
    select
      st.stunm '姓名'
    , sum(case cs.courseno when 'C001' then sc.scores end) '大学语文'
    , sum(case cs.courseno when 'C002' then sc.scores end) '新视野英语'
    , sum(case cs.courseno when 'C003' then sc.scores end) '离散数学'
    , sum(case cs.courseno when 'C004' then sc.scores end) '概率论与数理统计'
    , sum(case cs.courseno when 'C005' then sc.scores end) '线性代数'
    , sum(case cs.courseno when 'C006' then sc.scores end) '高等数学(一)'
    , sum(case cs.courseno when 'C007' then sc.scores end) '高等数学(二)'
    from score sc
      inner join student st on st.stuid = sc.stuid
      inner join courses cs on cs.courseno = sc.courseno
    group by st.stunm
     
    -- 方式二：使用pivot语法
    select *
    from (
      select st.stunm, cs.coursenm, sc.scores
      from score sc
        inner join student st on st.stuid = sc.stuid
        inner join courses cs on cs.courseno = sc.courseno ) a
    pivot (
      sum(scores) for coursenm
      in(大学语文, 新视野英语, 离散数学, 概率论与数理统计, 线性代数, [高等数学(一)], [高等数学(二)])
    ) pvt
     
    -- 方式三：动态sql
     
     

结果图示：

 

参考链接：

mysql行转列：https://blog.youkuaiyun.com/sinat_27406925/article/details/77507478

SQL Server行转列：https://www.cnblogs.com/no27/p/6398130.html
---------------------
作者：不会撒谎的乌索普
来源：优快云
原文：https://blog.youkuaiyun.com/hncu1306602liuqiang/article/details/82934250
版权声明：本文为博主原创文章，转载请附上博文链接！