sqlserver 縱橫

標籤：

縱錶轉橫表

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 Scoreselect 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 tgroup 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 ScoreHbfrom Score as tgroup by t.Nameselect * from dbo.ScoreHb-- union all連結3個科目select t.Name,‘語文‘ as Class,t.語文 as scorefrom ScoreHb as tunion allselect t.Name,‘數學‘ as Class,t.數學 as scorefrom ScoreHb as tunion allselect t.Name,‘物理‘ as Class,t.物理 as scorefrom ScoreHb as torder by t.Name desc

 

pivot縱錶轉橫表

select    t2.Name,    t2.數學,    t2.物理,    t2.語文from Score as t1pivot (sum(score) for Class in(數學,語文,物理)) as t2

unpivot 橫錶轉縱表

 select *fromScoreHb unpivot (分數 for 課程 in (語文,數學,物理)) as t4

 

sqlserver 縱橫