怎样在SQL存储过程中实现行转列报表_使用PIVOT函数动态生成-SQL-PHP中文网

SQL Server 的 PIVOT 不支持变量列名，必须用 STRING_AGG + QUOTENAME 拼接动态列并以 sp_executesql 执行；需处理空值、特殊字符、权限及执行计划缓存问题。

怎样在sql存储过程中实现行转列报表_使用pivot函数动态生成

PIVOT 不能直接用变量，动态列必须拼接 SQL 字符串

SQL Server 的 PIVOT 语法要求列名必须是**硬编码的常量**，不支持直接传入变量或子查询。想根据数据动态生成列（比如按月份、产品类别、用户标签），就必须用字符串拼接 + EXEC 或 sp_executesql 执行动态 SQL。

常见错误是写成这样，结果报错：
PIVOT (SUM(Amount) FOR Category IN (@cols)) —— @cols 是变量，SQL Server 直接拒绝解析。

动态列名必须先查出来，拼成类似 [A],[B],[C] 的字符串
整个 PIVOT 查询要包裹在 NVARCHAR(MAX) 变量里
务必用 sp_executesql（而非 EXEC）——它支持参数化，避免 SQL 注入且能复用执行计划
列名含空格或特殊字符时，要用方括号包裹，拼接前需用 QUOTENAME() 安全转义

如何安全拼出动态列名列表

从源数据中提取唯一值并转为 [Col1],[Col2] 格式，最稳妥的方式是用 STRING_AGG()（SQL Server 2017+）配合 QUOTENAME()：

SELECT @cols = STRING_AGG(QUOTENAME(Category), ',')  
FROM (SELECT DISTINCT Category FROM Sales) AS T;

如果版本低于 2017，可用 FOR XML PATH('') 替代，但要注意 NULL 值和特殊字符处理；QUOTENAME() 必须加，否则遇到 Category = 'North America' 就会拼出 [North America]（合法），但若值是 ] 或 ]]，不转义会破坏语法。

别漏掉 DISTINCT，重复列名会导致 PIVOT 报错
排序影响可读性，建议加 ORDER BY Category（STRING_AGG 支持，FOR XML 需在子查询里排）
空结果集时 @cols 会是 NULL，后续拼接会失败，需提前判断：IF ISNULL(@cols, '') = '' RAISERROR('无有效列名', 16, 1)

完整动态 PIVOT 存储过程模板

一个最小可行的存储过程骨架，包含输入参数、列生成、SQL 拼接和执行：

CREATE PROCEDURE usp_Report_SalesByCategory
    @Year INT = NULL
AS
BEGIN
    DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);
<pre class="brush:php;toolbar:false;">SELECT @cols = STRING_AGG(QUOTENAME(Category), ',')
FROM (SELECT DISTINCT Category FROM Sales WHERE ISNULL(@Year, Year) = Year) AS T;

IF ISNULL(@cols, '') = ''
    RETURN;

SET @sql = N'
    SELECT * FROM (
        SELECT Category, Amount, Year FROM Sales 
        WHERE ISNULL(@pYear, Year) = Year
    ) AS src
    PIVOT (SUM(Amount) FOR Category IN (' + @cols + N')) AS pvt;';

EXEC sp_executesql @sql, N'@pYear INT', @pYear = @Year;

END

@pYear 是传给动态 SQL 的参数名，和外部变量 @Year 名字可以不同，但类型必须一致
WHERE 条件里的 ISNULL(@Year, Year) 是为了兼容“不限年份”的调用场景，避免在动态 SQL 中拼 NULL 值判断逻辑
所有换行用 N' 开头，保证 Unicode 安全；字符串内不能有单引号，否则需双写（''）
执行前可用 PRINT @sql 调试，但生产环境记得删掉

性能与权限隐患：动态 SQL 不是万能解药

每次调用都生成新执行计划，缓存效率远低于静态 PIVOT。如果列值变化频繁（如每日新增标签），计划缓存可能被快速挤占。更严重的是权限问题：sp_executesql 以调用者上下文运行，存储过程拥有者权限不继承——调用者必须对 Sales 表有 SELECT 权限，否则报错 The SELECT permission was denied on the object 'Sales'。