create table Score

(

    Name nvarchar(128),

    Class nvarchar(128),

    score int

)

insert into Score(Name, Class, score)values('张三','语文',98)

insert into Score values('张三','语文',98)

insert into Score values('张三','数学',89)

insert into Score values('张三','物理',78)

insert into Score values('李四','语文',79)

insert into Score values('李四','数学',88)

insert into Score values('李四','物理',100)

select * from Score

select t.Name,

SUM(case t.Class when '语文' then t.score else 0 end) as 语文,

SUM(case t.Class when '数学' then t.score else 0 end) as 数学,

SUM(case t.Class when '物理' then t.score else 0 end) as 物理,

SUM(case t.Class when '外语' then t.score else 0 end) as 外语,

SUM(case t.Class when '政治' then t.score else 0 end) as 政治,

SUM(case t.Class when '体育' then t.score else 0 end) as 体育

from Score as t

group by t.Name

-- 转换的表插入新表

select t.Name,

SUM(case t.Class when '语文' then t.score else 0 end) as 语文,

SUM(case t.Class when '数学' then t.score else 0 end) as 数学,

SUM(case t.Class when '物理' then t.score else 0 end) as 物理

into ScoreHb

from Score as t

group by t.Name

select * from dbo.ScoreHb

-- union all链接3个科目

select t.Name,

'语文' as Class,

t.语文 as score

from ScoreHb as t

union all

select t.Name,

'数学' as Class,

t.数学 as score

from ScoreHb as t

union all

select t.Name,

'物理' as Class,

t.物理 as score

from ScoreHb as t

order by t.Name desc

select

    t2.Name,

    t2.数学,

    t2.物理,

    t2.语文

from Score as t1

pivot (sum(score) for Class in(数学,语文,物理)) as t2

 select

 *

from

ScoreHb

 unpivot (分数 for 课程 in (语文,数学,物理)) as t4