PIVOT 实现数据库中行列转换

清疚 2022-08-06 11:21 216阅读 0赞

本文转自：[http://www.cnblogs.com/yank/archive/2008/09/09/1287556.html][http_www.cnblogs.com_yank_archive_2008_09_09_1287556.html]

数据库中行列转换

PIVOT 和 UNPIVOT 关系运算符将表值表达式更改为另一个表。PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式，并在必要时对最终输出中所需的任何其余列值执行聚合。 PIVOT 提供的语法比一系列复杂的 SELECT...CASE 语句中所指定的语法更简单和更具可读性。

在我们进行复杂的查询统计的时候，特别是销售统计、处理大量数据的时候，PIVOT的作用就显得非常突出。

**案例分析：**在开发一个收集客户资源的小型系统时，需要对客户的资源进行查询统计，本来想用原来的统计解决方案，但是哥们提出了使用Pivot函数，这个我还真没用过，所以就针对这个函数进行了一些学习。

每一个客户资源通过不同的渠道进来，需要公司成员对用户的信息进行处理，回访、邮件之类的，所以客户信息的状态需要修改，而且需要对每一种状态的客户信息进行统计。如果按照旧的逻辑，采用简单的Count语句去查询统计，SQL语句如下：

Code  
select S.F\_status,count(S.F\_ID)as F\_Count from c2c.dbo.T\_Spread\_customer as S  
groupby S.F\_status

你得到的结果类似于：

F\_status F\_Count  
\----------- -----------  
NULL 4  
1 1  
4 2  
5 1  
6 5  
7 1

如果是查询整个数据表的统计信息，或许这样做也不是很麻烦，只需要遍历你得到的表，取出数据，然后匹配到某一状态就可以。但是，如果根据客户信息不同来源进行统计，显然这样做，局限性很大，我们没有办法一次性得到各个来源的统计信息。而**采用****PIVOT**，你会得到如下的结果：

F\_Num F\_Source F\_Total F\_Normal F\_Crm F\_Wait F\_InEffect F\_Effect  
\----------- ------- ----------- ----------- ----------- ----------- ----------- -----------  
1 First 3 0 2 0 0 1  
2 Second 3 0 0 1 0 2  
3 Third 3 0 0 0 1 2  
4 Forth 1 1 0 0 0 0  
5 Other 0 0 0 0 0 0

它把原来一列的数据，变成了Table的一行数据，而我们要展示给用户的也是这样一张表，所以利用此函数可以节省大量的逻辑代码。方便、快捷、高效。

具体的SQL语句如下：

select\*from  
(  
select S.F\_status,count(S.F\_ID)as F\_Count from c2c.dbo.T\_Spread\_customer as S  
groupby S.F\_status  
)As T  
  
PIVOT(sum(T.F\_Count) for T.F\_Status in(\[1\],\[4\],\[5\],\[6\],\[7\])) as C

在这里，我对其显示进行一些加工和判定：

加工后的SQL语句  
select F\_PsnID,isnull(\[1\],**0**)\+isnull(\[4\],**0**)\+isnull(\[5\],**0**)\+isnull(\[6\],**0**)\+isnull(\[7\],**0**) as'F\_Total',isnull(\[1\],**0**) as'F\_Normal',  
isnull(\[4\],**0**) as'F\_NormalCrm',isnull(\[5\],**0**) as'F\_Wait',isnull(\[7\],**0**) as'F\_InEffect',isnull(\[6\],**0**) as'F\_Effect'  
from (  
  
select S.F\_PsnID,S.F\_status,count(S.F\_ID)as F\_Count from c2c.dbo.T\_Spread\_customer as S  
where (**1**=**1**)  
groupby S.F\_PsnID,S.F\_status  
  
)As T  
  
PIVOT(sum(T.F\_Count) for T.F\_Status in(\[1\],\[4\],\[5\],\[6\],\[7\])) as C  
orderby F\_Total desc

当然这只是个简单的小例子，你可以使用PIVOT，然后进行加工处理，它可以实现更为强大的功能。我在应用的时候用到了加入了临时表、分页等功能。

UNPIVOT 与 PIVOT 执行相反的操作，将表值表达式的列转换为列值。但是在实际应用中，有些聚合之后的数据很难进行拆分。所以呢，UNPIVOT并非PIVOT的逆过程。

**建议：**如果你想了解的更加清楚，请参考：[http://technet.microsoft.com/zh-cn/library/ms177410.aspx][http_technet.microsoft.com_zh-cn_library_ms177410.aspx]  
**注意：**对升级到 SQL Server 2005 或更高版本的数据库使用 PIVOT 和 UNPIVOT 时，必须将数据库的兼容级别设置为 90 或更高。

有的SQL Server 2005初始安装时，默认的兼容级别为“80”，这时我们需要将兼容级别进行设置，不然，PIVOT不能正常的执行。我在使用PIVOT时就遇到这样的问题。

具体的修改方案如下：

修改兼容级别步骤  
1、连接到相应的 SQL Server 数据库引擎实例之后，在对象资源管理器中，单击服务器名称以展开服务器树。  
  
2、展开“数据库”，然后根据数据库的不同，选择用户数据库，或展开“系统数据库”，再选择系统数据库。  
  
3、右键单击数据库，再单击“属性”。  
  
“数据库属性”对话框将打开。  
  
4、在“选择页”窗格中，单击“选项”。  
  
当前兼容级别显示在“兼容级别”列表框中。  
  
5、若要更改兼容级别，请从列表中选择其他选项。可用选项包括 SQL Server 2000 (80)、SQL Server 2005 (90) 或 SQL Server 2008 (100)。

具体的兼容级别之间的差异请参考：[http://technet.microsoft.com/zh-cn/library/bb510680.aspx][http_technet.microsoft.com_zh-cn_library_bb510680.aspx]

[http_www.cnblogs.com_yank_archive_2008_09_09_1287556.html]: http://www.cnblogs.com/yank/archive/2008/09/09/1287556.html
[http_technet.microsoft.com_zh-cn_library_ms177410.aspx]: http://technet.microsoft.com/zh-cn/library/ms177410.aspx
[http_technet.microsoft.com_zh-cn_library_bb510680.aspx]: http://technet.microsoft.com/zh-cn/library/bb510680.aspx