Sql Server 行轉列

Sql Server行轉列示例：

--建立部門表，寫入資料
create table Table_Dep(
depid varchar(10),
dname varchar(50)
)
insert into Table_Dep values('1','國内業務一部')
insert into Table_Dep values('2','國内業務二部')
insert into Table_Dep values('3','國内業務三部')
insert into Table_Dep values('4','國際業務部')

--建立業績表，寫入資料
create table Table_Yeji(
mon varchar(20),
depid varchar(20),
yj varchar(30)
)
insert into Table_Yeji values('一月份','1','10')
insert into Table_Yeji values('一月份','2','10')
insert into Table_Yeji values('一月份','3','5')
insert into Table_Yeji values('二月份','2','8')
insert into Table_Yeji values('二月份','4','9')
insert into Table_Yeji values('三月份','3','8')

--輸出結果：部門、名稱、一月份、二月份、三月份
select A.depid,A.dname,B.一月份,B.二月份,B.三月份 from Table_Dep A left join 
(select depid,
MAX(case mon when'一月份' then yj else null end)'一月份',
MAX(case mon when'二月份' then yj else null end)'二月份',
MAX(case mon when'三月份' then yj else null end)'三月份'
from Table_Yeji group by depid) B on A.depid=B.depid

--pivot函數行轉列
select A.depid,A.dname,B.一月份,B.二月份,B.三月份 from Table_Dep A left join 
(select * from Table_Yeji pivot(max(yj) for mon in(一月份,二月份,三月份))tb) B on A.depid=B.depid