3.7 动态Pivot
作为另外一个练习，假设你要编写一个存储过程，它生成动态Pivot查询。这个存储过程接收这些参数（都是Unicode字符串类型）：@query、@on_rows、@on_cols、@agg_func和 @agg_col。你要根据这些输入构造PIVOT查询串并动态执行。下面是对输入参数的描述：
n @query 提供给PIVOT 运算符的查询或表/视图的名称。
n @on_rows 用作分组列的列/表达式列表。
n @on_cols 被旋转的列或表达式；该列中的不重复值将成为目标列的名称。
n @agg_func 聚合函数 (MIN、MAX、SUM、COUNT等) 。
n @agg_col 作为聚合函数的输入的列/表达式。
如果还没搞清楚要求和每个输入的含义，可以直接跳到代码清单7-8中的解决方案，分析代码清单后面的调用示例和输出以及对该解决方案所作的解释。然后在查看这个方案之前尝试提供自己的解决方案。
重要 该解决方案的编程实践非常糟糕，而且存在安全隐患。我将用这个解决方案讨论其中存在的缺陷，然后提出更健壮更安全的解决方案。
代码清单7-8显示了该任务的建议的解决方案。
代码清单7-8 创建存储过程sp_pivot的脚本
USE master;
GO
IF OBJECT_ID('dbo.sp_pivot') IS NOT NULL
DROP PROC dbo.sp_pivot;
GO
CREATE PROC dbo.sp_pivot
@query AS NVARCHAR(MAX),
@on_rows AS NVARCHAR(MAX),
@on_cols AS NVARCHAR(MAX),
@agg_func AS NVARCHAR(MAX) = N'MAX',
@agg_col AS NVARCHAR(MAX)
AS
DECLARE
@sql AS NVARCHAR(MAX),
@cols AS NVARCHAR(MAX),
@newline AS NVARCHAR(2);
SET @newline = NCHAR(13) + NCHAR(10);
-- 如果输入是有效的表或视图，则使用它构造SELECT语句
--
IF COALESCE(OBJECT_ID(@query, N'U'),
OBJECT_ID(@query, N'V')) IS NOT NULL
SET @query = N'SELECT * FROM ' + @query;
-- 把该查询用作派生表
SET @query = N'(' + @query + @newline + N' ) AS Query';
-- 处理@agg_col中的*
IF @agg_col = N'*'
SET @agg_col = N'1';
-- 构造列的列表
SET @sql =
N'SET @result = ' + @newline +
N' STUFF(' + @newline +
N' (SELECT N'','' + '
+ N'QUOTENAME(pivot_col) AS [text()]' + @newline +
N' FROM (SELECT DISTINCT('
+ @on_cols + N') AS pivot_col' + @newline +
N' FROM' + @query + N') AS DistinctCols' + @newline +
N' ORDER BY pivot_col' + @newline +
N' FOR XML PATH('''')),' + @newline +
N' 1, 1, N'''');'
EXEC sp_executesql
@stmt = @sql,
@params = N'@result AS NVARCHAR(MAX) OUTPUT',
@result = @cols OUTPUT; -- 创建 PIVOT 查询
SET @sql =
N'SELECT *' + @newline +
N'FROM' + @newline +
N' ( SELECT ' + @newline +
N' ' + @on_rows + N',' + @newline +
N' ' + @on_cols + N' AS pivot_col,' + @newline +
N' ' + @agg_col + N' AS agg_col' + @newline +
N' FROM ' + @newline +
N' ' + @query + @newline +
N' ) AS PivotInput' + @newline +
N' PIVOT' + @newline +
N' ( ' + @agg_func + N'(agg_col)' + @newline +
N' FOR pivot_col' + @newline +
N' IN(' + @cols + N')' + @newline +
N' ) AS PivotOutput;'
EXEC sp_executesql @sql;
GO
下面我通过这个练习来解释如何实现动态Pivot并讨论不良的编程实践和安全缺陷。我先讨论代码背后的逻辑，然后描述其中的不良的编程实践和缺陷并提出一个更健壮更安全的解决方案。
这个存储过程是在master中创建的特殊存储过程，这样它就可以在所有数据库中运行。之前提到过，动态执行是在当前数据库的上下文中调用的。这意味着该存储过程的代码实际上是在当前数据库的上下文中运行的，并与本地用户对象进行交互。
代码检查输入参数@query是否是有效的表或视图。如果是，代码使用它构造一个SELECT语句，并保存到@query中；如果不是，则假定它已经是一个查询。
然后通过在两边添加圆括号和派生表别名（AS Query）使该查询成为一个派生表。结果字符串保存到@query中。派生表将用于确定需要被旋转的不重复值（这些值位于输入参数@on_cols中保存的列/表达式），并作为PIVOT运算符的输入表表达式。
因为PIVOT运算符不支持*作为聚合函数的输入。例如，COUNT(*)—代码把@agg_col中的*替换为1。
然后代码串联一个动态查询字符串并保存到@sql变量中。这个字符串中的代码用于构造列列表（column list），它将用于PIVOT的IN子句。这个列列表通过FOR XML PATH查询构造。该查询串联@on_cols中的列/表达式的不重复值。
这个查询字符串（保存在@sql）被动态调用。动态代码通过一个输出参数返回一个包含列列表的字符串，并把它赋给变量@cols。
后面的代码构造真正的PIVOT查询字符串，并保存在变量@sql中。它对派生表（别名是Query）构造一个外部查询，这个派生表保存在@query中。外部查询创建另一个派生表PivotInput。外部查询中的SELECT列表包含下面的项：
n 保存在@on_rows中的分组列/表达式列表，PIVOT运算符利用它执行隐式的分组操作。
n 被旋转的列/表达式（保存在@on_cols），别名为pivot_col。
n 用作聚合函数输入的列（保存在@agg_col中），别名为agg_col。
PIVOT运算符基于派生表PivotInput。在PIVOT后面的圆括号包含下列各项：聚合函数（@agg_func）以及作为其输入的聚合列（agg_col），IN子句的圆括号中的列列表（@cols）。最外面的查询使用SELECT * 获取PIVOT操作返回的所有列。
最后，动态调用@sql变量中的PIVOT查询。
更多信息 关于PIVOT运算符更深入的讨论，请参考Inside
T-SQL Querying。
sp_pivot存储过程非常灵活，尽管这种灵活性也带来了非常高的安全成本，这一点我们将在后面讨论。为演示它的灵活性，我将提供三个示例，分别通过不同的输入调用它。你要确保学习并掌握了这些输入参数的意义。
下面的代码生成每个员工每年的订单合计，按订单月份旋转，它将生成表7-12所显示的输出。
EXEC Northwind.dbo.sp_pivot
@query = N'dbo.Orders',
@on_rows = N'EmployeeID AS empid, YEAR(OrderDate) AS order_year',
@on_cols = N'MONTH(OrderDate)',
@agg_func = N'COUNT',
@agg_col = N'*';
表7-12 按订单月份旋转的每个员工每年的订单合计
|
empid |
order_year |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
11 |
12 |
|
1 |
1996 |
0 |
0 |
0 |
0 |
0 |
0 |
1 |
5 |
5 |
2 |
4 |
9 |
|
2 |
1996 |
0 |
0 |
0 |
0 |
0 |
0 |
1 |
2 |
5 |
2 |
2 |
4 |
|
3 |
1996 |
0 |
0 |
0 |
0 |
0 |
0 |
4 |
2 |
1 |
3 |
4 |
4 |
续表
|
empid |
order_year |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
11 |
12 |
|
4 |
1996 |
0 |
0 |
0 |
0 |
0 |
0 |
7 |
5 |
3 |
8 |
5 |
3 |
|
5 |
1996 |
0 |
0 |
0 |
0 |
0 |
0 |
3 |
0 |
1 |
2 |
2 |
3 |
|
6 |
1996 |
0 |
0 |
0 |
0 |
0 |
0 |
2 |
4 |
3 |
0 |
3 |
3 |
|
7 |
1996 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
1 |
2 |
5 |
3 |
0 |
|
8 |
1996 |
0 |
0 |
0 |
0 |
0 |
0 |
2 |
6 |
3 |
2 |
2 |
4 |
|
9 |
1996 |
0 |
0 |
0 |
0 |
0 |
0 |
2 |
0 |
0 |
2 |
0 |
1 |
|
1 |
1997 |
3 |
2 |
5 |
1 |
5 |
4 |
7 |
3 |
8 |
7 |
3 |
7 |
|
2 |
1997 |
4 |
1 |
4 |
3 |
3 |
4 |
3 |
1 |
7 |
1 |
5 |
5 |
|
3 |
1997 |
7 |
9 |
3 |
5 |
5 |
6 |
2 |
4 |
4 |
7 |
8 |
11 |
|
4 |
1997 |
8 |
6 |
4 |
8 |
5 |
5 |
6 |
11 |
5 |
7 |
6 |
10 |
|
5 |
1997 |
0 |
0 |
3 |
0 |
2 |
2 |
1 |
3 |
2 |
3 |
1 |
1 |
|
6 |
1997 |
2 |
2 |
2 |
4 |
2 |
2 |
2 |
2 |
1 |
4 |
5 |
5 |
|
7 |
1997 |
3 |
1 |
2 |
6 |
5 |
1 |
5 |
3 |
5 |
1 |
1 |
3 |
|
8 |
1997 |
5 |
8 |
6 |
2 |
4 |
3 |
6 |
5 |
3 |
7 |
2 |
3 |
|
9 |
1997 |
1 |
0 |
1 |
2 |
1 |
3 |
1 |
1 |
2 |
1 |
3 |
3 |
|
1 |
1998 |
9 |
9 |
11 |
8 |
5 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
|
2 |
1998 |
7 |
3 |
9 |
18 |
2 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
|
3 |
1998 |
10 |
6 |
12 |
10 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
|
4 |
1998 |
6 |
14 |
12 |
10 |
2 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
|
5 |
1998 |
4 |
6 |
2 |
1 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
|
6 |
1998 |
3 |
4 |
7 |
5 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
|
7 |
1998 |
4 |
6 |
4 |
9 |
2 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
|
8 |
1998 |
7 |
2 |
10 |
9 |
3 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
|
9 |
1998 |
5 |
4 |
6 |
4 |
0 |
0 |
0 |
0 |