SQLServer行列转换

create table [Scores]
 (
      [ID] int identity(1,1) primary key,
      [Student] varchar(20) ,
      [Subjects] varchar(30),
      [Score] float
)

插入测试数据

  insert into Scores values('张三','语文','88')
  insert into Scores values('张三','英语','97')
  insert into Scores values('李四','语文','90')
  insert into Scores values('李四','英语','77')
  insert into Scores values('王五','语文','92')
  insert into Scores values('王五','英语','100')

1.原始数据状态

2.得到姓名，通过group by

  select Student as '姓名'
from Scores
group by Student
order by Student

3.再加上max， case……when

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

查看其它资料时，看到另外一种方法，用pivot

--group by, avg/max, pivot。这里用max和avg，结果都一样，有什么区别吗？有点不明白
--参考网上的资料，用法如下
/*
pivot(
　　聚合函数(要转成列值的列名)
　　for 要转换的列
　　in（目标列名）
　　)
*/
select Student as '姓名',
avg(语文) as '语文',
avg(英语) as '英语'
from Scores
pivot(
    avg(Score) for Subjects 
    in (语文,英语)
    )as NewScores
group by Student
order by Student asc