【转帖】PIVOT 用法浅尝

来源：互联网发布：软件项目工时统计表编辑：程序博客网时间：2024/06/06 19:00

原文地址http://hi.baidu.com/degilanvel/blog/item/07b4f245cab6ae46510ffe85.html

我觉得写的不错，分享下，呵呵

---------------------------------------------------------------------------------------------------------------------------------------------------

就个人感觉，PIVOT是用一个一维表数据建立一个二维表，一维表中包含X，Y坐标及由这两个坐标值确定的一行中的需要显示的数据；注意这个所用的一维表最好不可以包含多余数据，否则会影响结果。

清单一：建立表，以使用最少的情况作例子

create table PivotTest (OrgID varchar(max),UserID varchar(max),JE money)

insert into PivotTest values('1001','A',200)

insert into PivotTest values('1001','B',100)

insert into PivotTest values('1002','A',300)

insert into PivotTest values('1001','B',100)

insert into PivotTest values('1001','A',100)

insert into PivotTest values('1002','B',500)

insert into PivotTest values('1001','A',50)

insert into PivotTest values('1001','A',200)

insert into PivotTest values('1001','A',50)

insert into PivotTest values('1001','B',10)

select * from PivotTest

清单二：要求OrgID 为X轴，UserID为Y轴，统计每类情况的个数。

select OrgID,[A],[B]

from PivotTest a

Pivot (count(a.JE) FOR a.UserID in ([A],[B])) as PVT

输出结果

OrgID A B

1001 6 3
1002 1 1

这里使用的两个坐标外，用了另一个值来作统计；既然是统计数据，为什么不用X或Y坐标的值呢？因为多余出来的非坐标列将会影响输出结果:举例如下

select OrgID,[A],[B]

from PivotTest a

Pivot (count(a.UserID) FOR a.UserID in ([A],[B])) as PVT

输出结果：

OrgID A B

1001     0      1
1001     3      0
1001     1      2
1001     2      0
1002     1      0
1002     0      1

可见，在这样多处一列非坐标列的情况下，JE数目不同，则也会被当作单独的一个数值输出而不会加和。

则可以有两种方法，一种是使用非坐标列，另一种是不使用坐标列，后一种应这样使用：

select OrgID,[A],[B]

from

( select OrgID,UserID from PivotTest ---在这里剔除了JE列形成新表，剔除方法很实用

) a

Pivot (count(a.UserID) FOR a.UserID in ([A],[B])) as PVT

清单三：对要显示的数值进行统计求和

select OrgID,[A] as '高级用户',[B] as '普通用户'

from PivotTest a

Pivot (sum(a.JE) FOR a.UserID in ([A],[B])) as PVT

输出结果：

OrgID 高级用户普通用户

1001 650.00 210.00
1002 300.00 500.00

问题：如果这里被统计的数据外还有一列非轴数据，也会有影响么。？

清单四：再添加一列测试数据

create table PivotTest1 (OrgID varchar(max),UserID varchar(max),JE1 money,JE2 money)

insert into PivotTest1 values('1001','A',200,1)

insert into PivotTest1 values('1001','B',100,2)

insert into PivotTest1 values('1002','A',300,1)

insert into PivotTest1 values('1001','B',100,2)

insert into PivotTest1 values('1001','A',100,3)

insert into PivotTest1 values('1002','B',500,5)

insert into PivotTest1 values('1001','A',50,6)

insert into PivotTest1 values('1001','A',50,2)

insert into PivotTest1 values('1001','A',200,1)

insert into PivotTest1 values('1001','A',50,4)

insert into PivotTest1 values('1001','B',10,7)

select * from PivotTest1

清单五：同清单二做比较

select OrgID,[A],[B]

from PivotTest1 a

Pivot (count(a.JE1) FOR a.UserID in ([A],[B])) as PVT

输出结果：

OrgID A B

1001      2     0
1002      1     0
1001      1     2
1001      1     0
1001      1     0
1002      0     1
1001      1     0
1001      0     1

可见，只要不是count()中的字段，就会自动作为分类标准，这里1001 1 2 指的就是数据

1001   B   100.00    2.00
1001   B    100.00   2.00
1001   A    50.00     2.00

三行的统计值，其中OrgID，JE2自动作为纵向分类标准，UserID 作为横向分类标准。

验证：

select OrgID,[A],[B]

from PivotTest1 a

Pivot (count(a.JE2) FOR a.UserID in ([A],[B])) as PVT

输出结果：

OrgID A B

1001     0    1
1001     3    0
1001     1    2   --为 1001 B 100.00；1001 B 100.00；1001 A 100.00三行值的对应数据
1001     2     0
1002     1    0
1002     0    1

与

select OrgID,[A],[B]

from PivotTest a

Pivot (count(a.UserID) FOR a.UserID in ([A],[B])) as PVT

情况相同，即以OrgID和JE共做纵向分类标准。

清单六：同清单三对比

select OrgID,[A] as '高级用户',[B] as '普通用户'

from PivotTest1 a

Pivot (sum(a.JE1) FOR a.UserID in ([A],[B])) as PVT

输出结果

OrgID 高级用户普通用户

1001     400.00      NULL
1002     300.00      NULL
1001     50.00        200.00   --对应上面的说明
1001      100.00      NULL
1001      50.00        NULL
1002      NULL        500.00
1001      50.00       NULL
1001      NULL        10.00

select OrgID,[A] as '高级用户',[B] as '普通用户'

from PivotTest1 a

Pivot (sum(a.JE2) FOR a.UserID in ([A],[B])) as PVT

输出结果：

OrgID 高级用户普通用户

1001     NULL        7.00
1001     12.00      NULL
1001      3.00         4.00
1001    2.00        NULL
1002    1.00        NULL
1002    NULL        5.00

梳理完毕。感冒难受死我了。