行列转换

 

--建表
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

 

转载于:https://www.cnblogs.com/fjptwwf/p/5777216.html