SQL 实现行转列

1 、建表及插入数据

create table course(    student_name nvarchar(20) not null,    course_name nvarchar(20),    score int )insert into course(student_name,course_name,score)values('张三','语文',89)insert into course(student_name,course_name,score)values('张三','数学',80)insert into course(student_name,course_name,score)values('张三','英语',90)insert into course(student_name,course_name,score)values('李四','语文',91)insert into course(student_name,course_name,score)values('李四','数学',92)insert into course(student_name,course_name,score)values('王五','数学',90)insert into course(student_name,course_name,score)values('王五','英语',95)

course表数据如下

    现在想实现数据行转列，效果如下

姓名  语文  数学 英语

张三  89     80      90

李四  91     92

王五            90      95

2 、方法1:case when + sum()

select student_name,sum(case when course_name = '语文' then score    when course_name = '数学' then 0    when course_name='英语' then 0 end) as '语文',sum(case when course_name = '语文' then 0    when course_name = '数学' then score    when course_name='英语' then 0 end) as '数学',sum(case when course_name = '语文' then 0    when course_name = '数学' then 0    when course_name='英语' then score end) as '英语'from course group by student_name

查询结果如下

此种方式下，不存在的课程用0表示。

3、方法2: 使用多表关联

select distinct c.student_name, c1.score as '语文',c2.score as '数学',c3.score as '英语'from course cleft join (select student_name, score from course where course_name ='语文') c1 on c.student_name = c1.student_nameleft join (select student_name, score from course where course_name ='数学') c2 on c.student_name = c2.student_nameleft join (select student_name, score from course where course_name ='英语') c3 on c.student_name = c3.student_name

结果如下

此种方式下，不存在的课程用null表示。