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.csdn.net/sinat_27406925/article/details/77507478

SQL Server行轉列：https://www.cnblogs.com/no27/p/6398130.html

---------------------

作者：不會撒謊的烏索普

來源：CSDN

原文：https://blog.csdn.net/hncu1306602liuqiang/article/details/82934250

版權聲明：本文為部落客原創文章，轉載請附上博文連結！