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

 

转载于:https://www.cnblogs.com/Jackie-sky/p/5784402.html