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

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

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

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

使用动态 SQLiTbulo .com5FLyL1t

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

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

当您尝试从其自身用单引号分隔的字符串来生成 SQL 查询时，难度便增加了。您必须为比较字符串的前导单引号和尾随单引号提供转义序列。比较字符串包含在变量中，因此您必须使用 REPLACE() 函数以编程方式用两个单引号来替换它的一个单引号。但是，REPLACE() 函数也使用单引号作为其字符串型参数的字符串分隔符。因此，要在 @StoreName 变量中用两个单引号替换一个单引号，必须使用下面这个比较麻烦的语法iTbulo .com5FLyL1t

REPLACE(@StoreName,'',''')

单引号的 ASCII 编码为 39。为了提高清单 4 的代码中的 CASE 表达式的可读性，我有选择地使用 CHAR(39) 代替其中的某些单引号。iTbulo .com5FLyL1t

当代码完成书店名称的循环后，它会将 SQL 字符串的其余部分连接到 varchar 变量。当交叉表查询的 SQL 字符串完成后，代码将调用 EXECUTE 过程来生成交叉表结果集。iTbulo .com5FLyL1t

使用 ADO.NETiTbulo .com5FLyL1t

即使您已经克服了单引号的问题，还是必须与 varchar 变量的 8000 字符的限制作斗争。如果动态 SQL 字符串的长度超过 8000 个字符，查询就会崩溃。您可能认为 8000 个字符的长度对于您所要生成的任何查询都是足够的，但根据列数和每个 CASE 表达式的字符长度，最终的 SQL 字符串可能会变得很长。在我们的示例中，只要有大约 100 列，查询就会崩溃。您可以通过编程方式限制列数，这样查询就不会崩溃，但如果您需要超出这个限制，就没有办法了。iTbulo .com5FLyL1t

克服此限制的一个方法是利用 ADO.NET 的关系特性。下面的示例生成一个宽度可变的交叉表报表，该报表可以根据您的需要包括任意数量的列，而且无需使用 SQL 游标、动态 SQL 或 CASE 表达式。iTbulo .com5FLyL1t

清单 5（第 24 页）显示了此示例的 Visual Basic .NET 代码，该代码描述的是一个名为 XTabDotNetDS 的类。请注意，标注 A 中的 Inherits DataSet 语句在类的开头。如果您不熟悉 ADO.NET，则可以将数据集理解为一个小型的内存中数据库。DataSet 对象有一个 Tables 集合。ADO.NET 表被称为数据表。每个数据表有一个 Rows 集合，它包含 DataRow 对象和 Columns 集合，Columns 集合包含 DataColumn 对象。DataSet 对象还具有包含 DataRelation 对象的 Relations 集合，DataRelation 对象描述了数据表之间的引用关系。Inherits DataSet 语句确保自定义 DataSet 类的开头是 ADO.NET DataSet 基类的所有集合和功能。创建 XTabDotNetDS 对象时，该类的构造函数调用三个过程：Get3Tables