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 Scoresgroup by Studentorder by Student

结果展示

【第二种方法】

用pivot函数

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