正确利用-CSDN博客

本文链接：https://blog.csdn.net/zgqtxwd/article/details/2338659

表格报表以行的格式列出信息，这些行就像表的行一样。表格报表中的列标头与表的列名相关联。表格相交叉的报表（简称交叉表报表）是二维矩阵或电子表格，电子表格的查找标准是沿列标头的顶部向右以及沿行标头的左侧向下进行查找。您查找的数据—通常由诸如SUM()、AVERAGE()或COUNT()的聚合函数进行汇总—占据了矩阵的内部单元。

例如，假设您为全球知名的Pubs连锁书店工作，而该公司的首席执行官希望知道该公司的作者所著的书籍在公司每个书店的销售情况。您可以通过使用与清单1（第22页）所创建的视图相类似的视图来创建一个列数为3的表格报表。此视图名为vwSales，它列出了按书店和作者分组的图书总销售额，其中，每个作者—书店—销售额元组列在一行中。图1（第22页）显示了清单1的视图生成的表格报表。不过，如果您将报表呈现为交叉表报表（例如，第22页的图2显示的报表），则报表会更直观，并可以传递更多的信息。交叉表报表将书店名称在列标头中水平地列出，而不是沿着各行中的作者姓名列出。因此，要查找作者StearnsFeather在书店Bookbeat的销售额，应该看StearnsMacFeather行和Bookbeat列的交叉部分。

交叉表报表分为两类：宽度固定的报表和宽度可变的报表。对于宽度固定的报表，您在时就知道报表中的列数和列名。使用T-SQL查询来生成宽度固定的交叉表报表非常简单，因为您可以对聚合函数中嵌入的CASE表达式进行硬编码，从而对输出的每一列进行求值。清单2显示了针对我们举例的Pubs书店应用程序在宽度固定的交叉表查询中使用CASE表达式的示例。清单2中的第一个CASE表达式的含义是：如果Store列中的值等于"Barnum's"，则返回Sales列中的值；否则，返回0。交叉表查询在其列表达式中使用SUM()函数，因此无需预先聚合它用于其数据源的视图中的数据。所以，在其FROM子句中，清单2中的交叉表查询使用清单3创建的vwSales2视图，而不是vwSales。如果您使用COUNT()或AVG()作为聚合函数，则需要从CASE表达式中去掉"Else0"；否则，答案将对其销售值为0的交易进行平均和计数，而这样的交易实际上并不存在。当您从CASE表达式中去掉"Else0"后，对于不存在任何记录的作者—书店组合，将得到NULL值，而不是零。

但我们假设Pubs有一百家书店，每个月都有一些新书店开张，同时也有其他一些书店关张。由于这个原因，您需要一个宽度可变的交叉表报表，该报表从数据中动态地读取书店名称，无论当月存在多少书店，都为每个名称生成一个列。现在我们来探讨用于生成宽度可变的交叉表报表的两个完全不同的方法。第一个示例在存储过程中使用动态SQL来创建包含CASE表达式的交叉表查询字符串。EXEC命令执行该查询字符串，以便返回报表。第二个示例不使用CASE表达式或动态SQL，它使用ADO中新的关系特性—VisualStudio.NET中的数据访问组件—对数据进行交叉制表。下面我将顺便指出每种方法的优点和缺点。

使用动态SQL

清单4显示的存储过程procXTabDSQL使用vwSales2视图以及在整个书店名称列表中循环的本地游标生成交叉表查询字符串。该查询字符串以行标头作为第一列。然后，该过程在书店名称列表上打开游标。该游标将各个书店名称放到变量@StoreName中。每个迭代生成包含围绕CASE表达式的SUM()函数的字符串，然后将该字符串连接到varchar变量@strSQL。每个SUM()函数在最终报表中生成一列输出。

第一个SUM()函数中的CASE表达式用于报表的Barnum's书店列。将嵌在"Barnum's"中的单引号解释为比较字符串的分隔符，并尝试将stor_name中存储的数据与Barnum进行比较，而不是与Barnum's进行比较。要解决此问题，则必须将代码中名称的一个单引号替换为两个单引号。双单引号序列中的第一个引号是第二个引号的转义符；它告诉SQLServer字符串分析器将第二个引号解释为字符而不是字符串分隔符。列标志[Barnum's]中的单引号不会引起问题，因为该列标志是嵌入在中括号内的。清单2包含用于报表的Barnum's列的正确CASE表达式。