如何用动态列的表变量存储透视查询结果并关联其他查询？

首先明确：没法直接声明带动态列的表变量。SQL Server里表变量的结构必须在编译阶段就固定下来，不能在运行时动态添加或修改列，所以用表变量存动态透视结果这条路走不通。

针对你的需求，有个可行的替代方案：把关联逻辑直接整合到动态SQL脚本里，不需要中间存储结果，就能实现透视结果和其他查询的关联。具体操作如下：

操作步骤 #

1. 保留原有的动态列生成逻辑，先拿到透视需要的动态列列表@cols
2. 构造动态SQL时，将你要关联的查询（比如另一个表的查询）和透视结果通过固定列（比如task_effective_number）做JOIN，直接在动态SQL里完成关联

修改后的代码示例 #

假设你要关联的是DATABASE.dbo.sc_task表，获取该表的task_number和assigned_to字段，代码可以改成这样：

DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX);

-- 生成动态透视列
SELECT @cols = STUFF((SELECT DISTINCT ', ' + QUOTENAME(item.dv_item_option_new)
                      FROM DATABASE.dbo.sc_req_item req
                        INNER JOIN sc_item_option_mtom AS mtom ON mtom.request_item = req.sys_id
                        INNER JOIN sc_item_option AS item ON item.sys_id = mtom.sc_item_option
                        INNER JOIN item_option_new AS itemdef ON itemdef.sys_id = item.item_option_new
                      WHERE req.task_effective_number = 'RITM****'
                        AND item.dv_item_option_new IS NOT NULL
                        AND item.value IS NOT NULL
                      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')

-- 构造包含关联逻辑的动态SQL
SET @query = '
SELECT pvt.dv_stage, pvt.dv_state, pvt.dv_cat_item, pvt.task_effective_number, ' + @cols + ',
       task.task_number, task.assigned_to -- 关联表的字段
FROM 
(
    SELECT req.dv_stage, req.dv_state, req.dv_cat_item, req.task_effective_number, item.value, item.dv_item_option_new
    FROM DATABASE.dbo.sc_req_item req
      INNER JOIN sc_item_option_mtom AS mtom ON mtom.request_item = req.sys_id
      INNER JOIN sc_item_option AS item ON item.sys_id = mtom.sc_item_option
      INNER JOIN item_option_new AS itemdef ON itemdef.sys_id = item.item_option_new
    WHERE req.task_effective_number = ''RITM****''
      AND item.dv_item_option_new IS NOT NULL
      AND item.value IS NOT NULL
) p
PIVOT
(
    MAX(value)
    FOR dv_item_option_new IN (' + @cols + ')
) AS pvt
-- 关联另一个查询/表，这里以sc_task为例
INNER JOIN DATABASE.dbo.sc_task task 
    ON pvt.task_effective_number = task.ritm_number -- 替换成实际关联条件'

EXECUTE(@query)

补充说明 #

如果你的关联逻辑是复杂查询而不是简单表JOIN，也可以把那个查询写成子查询或者CTE，放到动态SQL里和透视结果关联。这种方式完全不需要用到tempdb，也避开了表变量静态结构的限制。

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