查询每个学生每门课程的成绩，sql server行转列实现

本人经常写sql server脚本，有时需要行转列，这里做个笔记。

练习脚本

-- 学生表
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