SQL server中转置函数（列转行PIVOT()函数、行转列UNPIVOT()函数）_unpivot函数sql

首先，通过两个表格说明本次要讲解的为什么要使用这两个函数？

图一：

图二：

客户的需求，就是希望行标题和列标题互换：保费项目和销售渠道对角线转置。

一、创建测试表：

create table  test_hangzhuanlie(
statdate varchar(8),--统计日期
branch_code varchar(6),--机构编号
mgr varchar(2),--销售渠道
longinsA [decimal](20, 2),--保费A
longinsB [decimal](20, 2),--保费B
longinsC [decimal](20, 2),--保费C
longinsD [decimal](20, 2),--保费D
stamp datetime--时间戳
)

二、增加测试数据：

--select * from test_hangzhuanlie
insert into test_hangzhuanlie (statdate, branch_code, mgr, longinsA, longinsB , longinsC, longinsD, stamp) 
values ('20190201', '110000', '1', '100', '100', '100', '100', GETDATE())
insert into test_hangzhuanlie (statdate, branch_code, mgr, longinsA, longinsB , longinsC, longinsD, stamp) 
values ('20190201', '120000', '1', '200', '200', '200', '200', GETDATE())
insert into test_hangzhuanlie (statdate, branch_code, mgr, longinsA, longinsB , longinsC, longinsD, stamp) 
values ('20190201', '130000', '1', '300', '300', '300', '300', GETDATE())
insert into test_hangzhuanlie (statdate, branch_code, mgr, longinsA, longinsB , longinsC, longinsD, stamp) 
values ('20190201', '140000', '1', '400', '400', '400', '400', GETDATE())
insert into test_hangzhuanlie (statdate, branch_code, mgr, longinsA, longinsB , longinsC, longinsD, stamp) 
values ('20190201', '150000', '1', '500', '500', '500', '500', GETDATE())
 
insert into test_hangzhuanlie (statdate, branch_code, mgr, longinsA, longinsB , longinsC, longinsD, stamp) 
values ('20190201', '110000', '2', '100', '100', '100', '100', GETDATE())
insert into test_hangzhuanlie (statdate, branch_code, mgr, longinsA, longinsB , longinsC, longinsD, stamp) 
values ('20190201', '120000', '2', '200', '200', '200', '200', GETDATE())
insert into test_hangzhuanlie (statdate, branch_code, mgr, longinsA, longinsB , longinsC, longinsD, stamp) 
values ('20190201', '130000', '2', '300', '300', '300', '300', GETDATE())
insert into test_hangzhuanlie (statdate, branch_code, mgr, longinsA, longinsB , longinsC, longinsD, stamp) 
values ('20190201', '140000', '2', '400', '400', '400', '400', GETDATE())
insert into test_hangzhuanlie (statdate, branch_code, mgr, longinsA, longinsB , longinsC, longinsD, stamp) 
values ('20190201', '150000', '2', '500', '500', '500', '500', GETDATE())
 
insert into test_hangzhuanlie (statdate, branch_code, mgr, longinsA, longinsB , longinsC, longinsD, stamp) 
values ('20190201', '110000', '3', '100', '100', '100', '100', GETDATE())
insert into test_hangzhuanlie (statdate, branch_code, mgr, longinsA, longinsB , longinsC, longinsD, stamp) 
values ('20190201', '120000', '3', '200', '200', '200', '200', GETDATE())
insert into test_hangzhuanlie (statdate, branch_code, mgr, longinsA, longinsB , longinsC, longinsD, stamp) 
values ('20190201', '130000', '3', '300', '300', '300', '300', GETDATE())
insert into test_hangzhuanlie (statdate, branch_code, mgr, longinsA, longinsB , longinsC, longinsD, stamp) 
values ('20190201', '140000', '3', '400', '400', '400', '400', GETDATE())
insert into test_hangzhuanlie (statdate, branch_code, mgr, longinsA, longinsB , longinsC, longinsD, stamp) 
values ('20190201', '150000', '3', '500', '500', '500', '500', GETDATE())

三、通过两个函数求得所要求的数据展示表格：

select statdate, branch_code, name， [1] as '1个险渠道', [2] as '2银保渠道', [3] as '3团险渠道'
from
(
select * from 
(
select statdate, branch_code, mgr, target, 
	case 
		when [column] = 'longinsA' then '保费A'
		when [column] = 'longinsB' then '保费B'
		when [column] = 'longinsC' then '保费C'
		when [column] = 'longinsD' then '保费D'
	end as name
from 
(		
select statdate, branch_code, mgr, 
SUM(longinsA) as longinsA, SUM(longinsB) as longinsB, SUM(longinsC) as longinsC, SUM(longinsD) as longinsD
from test_hangzhuanlie a group by statdate, branch_code, mgr
) a
unpivot (target FOR [column] IN ([longinsA], [longinsB], [longinsC], [longinsD])) AS t
) b
pivot(max(target) for mgr in ([1],[2],[3])) p
) x

总结：通过两个函数就可以得到上面要求的表格格式。