SQLserver集散置函数（列半路出家PIVOT()变量值、行转列UNPIVOT()函数）

首先，通过两个表格解释为什么在本讲解中使用这两个函数？

图一：

时间

机构号

销售渠道

保费A

保费B

保费C

保费D

statdate

branch_code

mgr

longinsA

longinsB

longinsC

longinsD

20190201

110000

1

100

100

100

100

20190201

110000

2

100

100

100

100

20190201

110000

3

100

100

100

100

图二：

时间

机构号

保费项目

1个险渠道

2银保渠道

3团险渠道

20190201

110000

保费A

100

100

100

20190201

110000

保费B

100

100

100

20190201

110000

保费C

100

100

100

20190201

110000

保费D

100

100

100

客户的需求是希望行标题和列标题互换：优质商品和销售渠道的对角移位。

1.创建测试表：

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--时间戳
)

2.添加测试数据：

--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())

3.通过两个函数获取所需的数据表示表：

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

摘要：上面需要的表格格式可以通过两个函数获得。