sql语句行列转至

【第一种方法】

1，查询原始的数据

select * from Scores

结果展示：

2，科目subject列转行

select Student as '姓名',
max(case Subject when '语文' then Score else 0 end) as '语文' ,--如果这个行是“语文”，就选此行作为列
max(case Subject when '英语' then Score else 0 end ) as '英语'
from Scores
group by Student
order by Student

结果展示

【第二种方法】

用pivot函数

/*
pivot(
　　聚合函数(要转成列值的列名)
　　for 要转换的列
　　in（目标列名）
　　)
*/
select Student as '姓名',
avg(语文) as '语文',
avg(英语) as '英语'
from Scores
pivot(
    avg(Score) for Subject 
    in (语文,英语)
    )as NewScores
group by Student
order by Student asc