SQL动态交叉表

动态交叉表就是列表可以根据表中数据的情况动态创建列。

    动态查询不能使用Select语句实现，它可以利用存储过程实现。思路是：

     首先检索列头信息，形成一个游标，然后遍历游标，将上面静态交叉表实现过程中使用Case语句判断的内容用游标里的值替代，形成一条新的Sql查询语句，然后执行并返回结果。下面是一个通用的实现动态交叉表的存储过程：

 

CREATE procedure proc_across_table @TableName as varchar(50),       --生成交叉表依据的表名 @NewColumn as varchar(50),       --生成表头依据的字段名 @GroupColumn as varchar(50),     --分组依据的字段名 @StatColumn as varchar(50),      --欲统计的字段名 @Operator as varchar(10)         --统计的运算方式 AS DECLARE @SQL as varchar(1000), @Column as varchar(50) --定义参数 EXECUTE ('DECLARE cursor_new_column CURSOR FOR SELECT DISTINCT ' + @NewColumn + ' from ' + @TableName + ' for read only ') --定义游标 begin   SET nocount ON    SET @SQL='select ' + @GroupColumn + ', ' + @Operator + '(' + @StatColumn + ') AS [' + @Operator + ' of ' + @StatColumn + ']' --定义SQL语句头   OPEN cursor_new_column   while (0=0)   BEGIN --遍历游标     FETCH NEXT FROM cursor_new_column INTO @Column --通过游标获取列头信息     if (@@fetch_status<>0) break         SET @SQL = @SQL + ', ' + @Operator + '(CASE ' + @NewColumn + ' WHEN ''' + @Column + ''' THEN ' + @StatColumn + ' ELSE Null END) AS [' + @Column + ']' --循环追加SQL语句   END SET @SQL = @SQL + ' from ' + @TableName + ' group by ' + @GroupColumn --定义SQL语句尾   EXECUTE(@SQL) --执行SQL语句   PRINT @SQL --输出SQL语句   IF @@error <>0 RETURN @@error --如果出错，则返回错误代码   CLOSE cursor_new_column --关闭游标   DEALLOCATE cursor_new_column RETURN 0 --释放游标，释放成功则返回0 end GO

    在SQL Server查询分析器上执行下面的代码，就可以得到动态交叉表。

 

proc_across_table '商品销售表', '订单号', '商品名', '订货数量', 'SUM'

   在SQL Server查询分析器上调用存储过程时，最前面的为将要调用的存储过程的名称，后面为执行存储过程需要的参数，参数用引号括起，存储过程名称与参数之间用空格分隔，参数之间用逗号分隔。

    说明：这是一个通用的存储过程，只要正确的传入生成交叉表依据的表名（@TableName）、生成表头依据的字段名（@NewColumn）、生成主键列依据的字段名（@GroupColumn）、欲统计的字段名（@StatColumn）和统计的运算方式（@Operator），就可以成功的将其应用到任何数据表中。

    

    实现动态交叉表时用到了游标，下面对游标进行详细介绍。

每个游标有4个组成部分，这4个组成部分必须符合下面的顺序：

（1）声明游标；

（2）打开游标；

（3）从一个游标中查找信息；

（4）关闭游标。

    其中，声明游标主要使用DECLARE CURSOR语句，下面介绍该语句。

语法：

DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR FOR select_statement [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]

• cursor_name
• INSENSITIVE：定义一个游标，以创建将由该游标使用的数据的临时副本。对游标的所有请求都从tempdb中的临时表中得到应答；因此，对游标进行提取操作时，返回的数据中不反映对基表所做的修改，并且游标不允许修改。使用SQL-92语法时，如果省略INSENSITIVE，（任何用户）对基表提交的删除和更新都反映在后面的提取中。
• SCROLL：指定所有的提取选项（FIRST、LAST、PRIOR、NEXT、RELATIVE和ABSOLUTE）均可用。如果未指定SCROLL，则NEXT是惟一支持的提取选项。
• select_statement：定义游标结果集的标准SELECT 语句。在游标声明的select_statement内不允许使用关键字COMPUTE、COMPUTE BY、FOR BROWSE和INTO。
• READ ONLY：表明不允许游标内的数据被更新，尽管在默认状态下游标是允许更新的。在UPDATE或DELETE语句的WHERE CURRENT OF子句中不允许引用游标。
• UPDATE [OF column_name [,...n]]：定义游标内可更新的列。如果指定OF column_name [,...n] 参数，则只允许修改所列出的列。如果在UPDATE中未指定列，则可以更新所有列。

      上面介绍的是SQL-92的游标语法规则，下面介绍SQL Server提供的扩展了的游标声明语法，它通过增加另外的保留字，使游标的功能进一步得到增强，其语法为：

DECLARE cursor_name CURSOR  [ LOCAL | GLOBAL ]  [ FORWARD_ONLY | SCROLL ]  [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] [ TYPE_WARNING ] FOR select_statement [ FOR UPDATE [ OF column_name [ ,...n ] ] ]

• cursor_name
• LOCAL：指定对于在其中创建的批处理、存储过程或触发器来说，该游标的作用域是局部的，游标名称仅在这个作用域内有效。在批处理、存储过程、触发器或存储过程OUTPUT参数中，游标可由局部游标变量引用。OUTPUT参数用于将局部游标传递回调用批处理、存储过程或触发器，它们可在存储过程终止后给游标变量分配参数使其引用游标。除非OUTPUT参数将游标传递回来，否则游标将在批处理、存储过程或触发器终止时隐式释放。如果OUTPUT参数将游标传递回来，则游标在最后引用它的变量释放或离开作用域时释放。
• GLOBAL：指定游标的作用域对连接来说是全局的。在由连接执行的任何存储过程或批处理中，都可以引用该游标名称，该游标仅在断开连接时隐式释放。

             

• FORWARD_ONLY：指定游标只能从第一行滚动到最后一行。FETCH NEXT是惟一受支持的提取选项。如果在指定FORWARD_ONLY时不指定STATIC、KEYSET和DYNAMIC关键字，则游标作为DYNAMIC游标进行操作。如果FORWARD_ONLY和SCROLL均未指定，则除非指定STATIC、KEYSET或DYNAMIC关键字，否则默认为FORWARD_ONLY。STATIC、KEYSET和DYNAMIC游标默认为SCROLL。与ODBC和ADO这类数据库API不同，STATIC、KEYSET和DYNAMIC Transact-SQL游标支持FORWARD_ONLY。
• STATIC：定义一个游标，以创建将由该游标使用的数据的临时副本。对游标的所有请求都从tempdb中的临时表中得到应答；因此，在对游标进行提取操作时，返回的数据中不反映对基表所做的修改，并且游标不允许修改。
• KEYSET：指定当游标打开时，游标中行的成员身份和顺序已经固定。对行进行惟一标识的键集内置在tempdb内一个称为keyset的表中。

        

 

   对基表中的非键值所做的更改（由游标所有者更改或由其他用户提交）可以在用户滚动游标时看到。其他用户执行的插入是不可见的（不能通过Transact-SQL服务器游标执行插入）。如果删除行，则在尝试提取行时返回值为-2的@@FETCH_STATUS。从游标以外更新键值类似于删除旧行然后再插入新行。具有新值的行是不可见的，并在尝试提取具有旧值的行时，将返回值为-2的@@FETCH_STATUS。如果通过指定WHERE CURRENT OF子句利用游标来完成更新，则新值是可见的。

• DYNAMIC：定义一个游标，以反映在滚动游标时对结果集内的各行所做的所有数据更改。行的数据值、顺序和成员身份在每次提取时都会更改。动态游标不支持ABSOLUTE提取选项。
• FAST_FORWARD：指定启用了性能优化的FORWARD_ONLY、READ_ONLY游标。如果指定了SCROLL或FOR_UPDATE，则不能指定FAST_FORWARD。

         

 

• READ_ONLY：禁止通过该游标进行更新。在UPDATE或DELETE语句的WHERE CURRENT OF子句中不能引用游标。
• SCROLL_LOCKS：指定通过游标进行的定位更新或删除保证会成功。将行读取到游标中以确保它们对随后的修改可用时，SQL Server将锁定这些行。如果指定了FAST_FORWARD，则不能指定SCROLL_LOCKS。
• OPTIMISTIC：指定如果行自从被读入游标以来已得到更新，则通过游标进行的定位更新或定位删除不会成功。当将行读入游标时SQL Server不会锁定行。相反，SQL Server使用timestamp列值的比较，或者如果表没有timestamp列，则使用校验和值，以确定将行读入游标后是否已修改该行。如果已修改该行，则尝试进行的定位更新或删除将失败。如果指定了FAST_FORWARD，则不能指定OPTIMISTIC。
• TYPE_WARNING：指定如果游标从所请求的类型隐式转换为另一种类型，则向客户端发送警告消息。
• select_statement：定义游标结果集的标准SELECT语句。在游标声明的select_statement内不允许使用关键字COMPUTE、COMPUTE BY、FOR BROWSE和INTO。

        如果select_statement中的子句与所请求的游标类型的功能有冲突，则SQL Server会将游标隐式转换为其他类型。

• FOR UPDATE [OF column_name [,...n]]：定义游标中可更新的列。如果提供了OF column_name [,...n]，则只允许修改列出的列。如果指定了UPDATE，但未指定列的列表，则除非指定了READ_ONLY并发选项，否则可以更新所有的列。

注意：在SQL Server 2000中，FAST_FORWARD和FORWARD_ONLY游标选项是相互排斥的。如果指定了其中的一个，则不能指定另一个，否则会引发错误。在SQL Server 2005中，这两个关键字可以用在同一个DECLARE CURSOR语句中。注意：如果查询引用了至少一个无惟一索引的表，则键集游标将转换为静态游标。注意：如果GLOBAL和LOCAL参数都未指定，则默认值由default to local cursor数据库选项的设置控制。在SQL Server 7.0版中，该选项默认为FALSE，以便与SQL Server的早期版本相匹配，在早期版本中，所有游标都是全局的，该选项的默认值在以后的SQL Server版本中可能会更改。 ：定义的Transact-SQL服务器游标的名称。cursor_name必须符合标识符规则。 ：用于指定游标的名称。