带输入变量的动态Pivot结果与其他表关联的解决方案咨询

针对你遇到的这个场景——需要把依赖输入变量的动态Pivot结果和其他表做关联，这里有两种实用的解决方案，我一个个给你拆解：

方案1：将关联逻辑整合到动态SQL中 #

这是最直接的方式，把你需要的关联逻辑直接嵌入到动态生成的查询语句里，而不是分开执行Pivot再关联。你可以修改现有的存储过程，让它输出包含完整关联逻辑的动态SQL，或者直接在外部构建完整的动态语句。

修改存储过程版本 #

调整SpFnGetSumScoreQuery，让它生成包含与period、RegionPeriodScore关联的完整查询：

ALTER PROCEDURE [dbo].[SpFnGetSumScoreQuery]
    @regionId nvarchar(255),
    @output nvarchar(max) output
AS
BEGIN
    DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX)
    
    -- 动态获取Pivot列（替换硬编码的1为输入变量@regionId）
    select @cols = STUFF((SELECT ',' + QUOTENAME(CategoryId)
                          from FnGetSumScoreHelper(@regionId)
                          group by CategoryId
                          order by CategoryId
                          FOR XML PATH(''), TYPE
                         ).value('.', 'NVARCHAR(MAX)')
                        ,1,1,'')
    
    -- 生成包含关联逻辑的完整动态查询
    set @output = '
        SELECT 
            p.Id as PeriodId,
            p.[Name] as PeriodName,
            rps.AdminScore,
            rps.UserScore,
            pivoted.*
        from [period] p
        left join RegionPeriodScore rps on p.Id = rps.PeriodId
        left join (
            SELECT PeriodId,' + @cols + ' 
            from (
                select CategoryId, PeriodId, Score from FnGetSumScoreHelper(' + @regionId + ')
            ) x
            pivot(
                sum(Score) for CategoryId in (' + @cols + ')
            ) pvt
        ) pivoted on rps.periodid = pivoted.periodid
    '
END

然后执行的时候直接调用存储过程拿到完整查询，再执行：

DECLARE @fullQuery nvarchar(max)
EXEC SpFnGetSumScoreQuery '1', @fullQuery OUTPUT
EXEC sp_executesql @fullQuery

注意：原来的存储过程里硬编码了FnGetSumScoreHelper(1)，一定要改成用输入变量@regionId，不然传入的参数根本没生效！

方案2：用临时表存储动态Pivot结果，再关联 #

如果你不想修改原存储过程，也可以先把动态Pivot的结果存入临时表，然后用临时表和其他表做关联。这里要注意动态SQL和临时表的作用域：

DECLARE @SumScoreCategories nvarchar(max), @regionId nvarchar(255) = '1'

-- 第一步：获取动态Pivot查询语句
EXEC SpFnGetSumScoreQuery @regionId, @SumScoreCategories OUTPUT

-- 第二步：用动态SQL将Pivot结果存入临时表
EXEC sp_executesql N'
    SELECT * INTO #PivotedSumScores
    FROM (' + @SumScoreCategories + ') t
'

-- 第三步：关联临时表和其他表
SELECT 
    p.Id as PeriodId,
    p.[Name] as PeriodName,
    rps.AdminScore,
    rps.UserScore,
    pss.*
from [period] p
left join RegionPeriodScore rps on p.Id = rps.PeriodId
inner join #PivotedSumScores pss on rps.periodid = pss.periodid

-- 清理临时表
DROP TABLE #PivotedSumScores

提示：表变量因为需要提前定义固定结构，没法适配动态Pivot的不确定列，所以临时表是更适合的选择。

关键安全提示 #

如果@regionId是用户输入的内容，一定要注意SQL注入风险。推荐用参数化方式传递变量，比如把方案1的执行逻辑改成：

DECLARE @fullQuery nvarchar(max), @regionId nvarchar(255) = '1'
EXEC SpFnGetSumScoreQuery @regionId, @fullQuery OUTPUT

-- 用参数化执行动态SQL，避免注入
EXEC sp_executesql @fullQuery, N'@regionParam nvarchar(255)', @regionParam = @regionId

内容的提问来源于stack exchange，提问作者farhang67