SQL Server 2005新功能之PIVOT的描述

以下的文章主要描述的是SQL Server 2005新功能之PIVOT，在工具的升级中，我个人认为首先我们的看看这个工具，其主要是在哪些功能上得到加强，所以今天我们就来看看SQL2005这个PIVOT吧。PIVOT 关系运算符对表值表达式进行操作以获得另一个表。

PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来转换表。工具的升级，我以为得先看看这个工具在哪些功能上得到加强，今天我们就看看SQL2005这个PIVOT吧。PIVOT 关系运算符对表值表达式进行操作以获得另一个表。PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来转换表值表达式，并在必要时对最终输出中所需的任何其余的列值执行聚合。

记得我们在SQL2000中要用聚合和CASE语句完成一个行列转换吧，特别当待转成列的数据不定时，我们往往构造动态SQL，然后用EXEC来运行。

环境准备：

-- Author: happyflsytone   
-- Version:V1.001   
-- Date:2008-09-18 10:20:53   
-- Test Data: ta   
IF OBJECT_ID('ta') IS NOT NULL   
DROP TABLE ta   
;   
CREATE TABLE ta(id INT,col1 Nvarchar(2),col2 Nvarchar(2),col3 Nvarchar(4),col4 INT)   
;   
INSERT INTO ta   
SELECT 1,'HN','CS','abc',1 UNION ALL   
SELECT 2,'HN','CS','abcd',2 UNION ALL   
SELECT 3,'HN','CD','abcd' ,3UNION ALL   
SELECT 4,'HN','HY','ae' ,4   
;

我们先来回顾SQL2000的行列转换,比如我们对上例程把col3转列显示，并把col4的和当对应列值。我们分两种情况来讨论：

一、当col3的列值固定就是’abc’,’abcd’,’ae’三种情况

SELECT   
col1,   
col2,   
[abc] = SUM(CASE WHEN col3 = 'abc' THEN col4 ELSE 0 END),   
[abcd] = SUM(CASE WHEN col3 = 'abcd' THEN col4 ELSE 0 END),   
[ae] = SUM(CASE WHEN col3 = 'ae' THEN col4 ELSE 0 END)   
FROM ta   
GROUP BY col1,col2   
/*   
col1 col2 abc abcd ae   
HN CD 0 3 0   
HN CS 1 2 0   
HN HY 0 0 4

(3 行受影响)

二、当col3的列值不固定时就运用动态SQL，其实也就是构造一个sum(CASE WHEN …)SQL字符串

DECLARE @s varchar(8000)   
SELECT @s = isnull(@s+',   
','') +'['+col3+'] = SUM(CASE WHEN col3 = '''+col3+''' THEN col4 ELSE 0 END)'   
FROM ( SELECT distinct col3 FROM ta) a   
SET @s = 'SELECT   
col1,   
col2,   
'+@s + '   
FROM ta   
GROUP BY   
col1,col2'   
EXEC(@s)   
/*   
col1 col2 abc abcd ae   
HN CD 0 3 0   
HN CS 1 2 0   
HN HY 0 0 4

(3 行受影响)

我们先输入这个@S看看是什么东东，只要加上print @s

SELECT   
col1,   
col2,   
[abc] = SUM(CASE WHEN col3 = 'abc' THEN col4 ELSE 0 END),   
[abcd] = SUM(CASE WHEN col3 = 'abcd' THEN col4 ELSE 0 END),   
[ae] = SUM(CASE WHEN col3 = 'ae' THEN col4 ELSE 0 END)   
FROM ta   
GROUP BY   
col1,col2

其实就是上面我们构造的固定列值的SQL嘛。

好，现在们开始在2005中实现这个功能，先来看看2005的FROM子句的定义(关于如何看这个定义请参照SQL2005的文档约定及Transate-SQL语法约定)：

[ FROM { <table_source> } [ ,...n ] ]   
<table_source> ::=   
{   
<pivoted_table>   
}   
<pivoted_table> ::=   
table_source PIVOT <pivot_clause> table_alias   
<pivot_clause> ::=   
( aggregate_function ( value_column )   
FOR pivot_column   
IN ( <column_list> )   
)   
<column_list> ::=   
column_name [ , ... ]

pivot_column 和 value_column 是 PIVOT 运算符使用的组合列。PIVOT 遵循以下过程获得输出结果集：

对分组列的 input_table 执行 GROUP BY，为每个组生成一个输出行。

输出行中的分组列获得 input_table 中该组的对应列值。

通过执行以下操作，为每个输出行生成列列表中的列的值：

针对 pivot_column，对上一步在 GROUP BY 中生成的行另外进行分组。

对于 column_list 中的每个输出列，选择满足以下条件的子组：

pivot_column = CONVERT(<data type of pivot_column>, 'output_column')

针对此子组上的 aggregate_function 对 value_column 求值，其结果作为相应的 output_column 的值返回。如果该子组为空，SQL Server 2005 将为该 output_column 生成空值。如果聚合函数是 COUNT，且子组为空，则返回零 (0)。

接着我们利用我们开头的例子来理解一下这个FROM子句，很显然我们的col4对应上面的value_column,我们还假定列会下固定为这三项，那么列 col3 对应上面的pivot_column,进而我们应该得出[abc],[abcd],[ae]是column_name即我们的输出列，最后我们只要构造一下table_source就可以了，如何构造这个table_source，显然pivot_column 和 value_column应该包含在其中，其它就应该是你想要分组的列啦.

我们来总结一下：这个FROM子句是基于 table_source 对 pivot_column 进行透视，table_source 中 pivot_column 和 value_column 列之外的列被称为透视运算符的组合列,而PIVOT 是对输入表执行组合列的分组操作，并为每个组返回一行，好，我们试着写出这个SQL：

SELECT col1,col2,[abc],[abcd],[ae]   
FROM   
(SELECT col1,col2,col3,col4   
FROM ta ) p   
PIVOT   
( SUM (col4)   
FOR col3 IN ([abc],[abcd],[ae])   
)AS unpvt

我们执行一下看看结果：