行列转换

 

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

 

insert into Scores(Student,Subject,Score) values('张三','语文','90')

 

方法一：

select Student AS '姓名' ,
max(CASE Subject when '语文' then Score else 0 end) 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

 

方法二（据说Sql Server2005以下的不能用）：

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