sqlserver 纵横

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

 

pivot纵表转横表

select
    t2.Name,
    t2.数学,
    t2.物理,
    t2.语文
from Score as t1
pivot (sum(score) for Class in(数学,语文,物理)) as t2

unpivot 横表转纵表

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

 

sqlserver 纵横

标签：