SQL动态透视下如何声明含动态列的表变量以完成关联查询

核心问题 #

静态声明的表变量、固定结构临时表，都需要在编写代码时就明确写死所有列的名称和数据类型，但动态PIVOT的输出列是运行时从业务表读取的、没有固定值，从语法层面根本无法直接用提前声明的表变量存结果。

可落地方案 #

方案1：全逻辑拼入动态SQL（推荐） #

不要拆分“透视计算”和“表关联”两个步骤，直接把需要关联的目标表、JOIN条件全部拼到动态SQL的查询字符串里，整个关联逻辑在动态SQL运行时完成，完全绕开“需要提前定义表结构存结果”的限制。
先修正你现有代码里的动态列拼接bug：你当前写的列生成逻辑加了多余的GROUP BY ColName, ProposalID，会导致同一个列名被重复拼接，直接去掉冗余的分组条件即可：

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

-- 生成不重复的动态透视列清单
SELECT @cols = STUFF(
  (SELECT DISTINCT ',' + QUOTENAME(ColName)
   FROM [sbs].[ProposalAmounts]
   FOR XML PATH(''), TYPE
  ).value('.', 'NVARCHAR(MAX)'),1,1,'')

-- 动态拼接查询时直接嵌入JOIN逻辑
SET @query = N'
SELECT pvt.ProposalID, ' + @cols + N', t.关联字段1, t.关联字段2
FROM (
  select ProposalID, Amount, ColName 
  from [sbs].[ProposalAmounts]
) x
PIVOT (
  MAX(Amount) for ColName in (' + @cols + N')
) pvt
-- 直接把目标关联表的JOIN条件写在这里
INNER JOIN [sbs].[你的目标关联表] t
  ON pvt.ProposalID = t.ProposalID
'
EXEC sp_executesql @query;

这种方式不需要额外创建中间存储对象，动态SQL运行时会自动匹配透视输出的列结构，直接完成关联查询，没有语法兼容问题。

方案2：动态创建临时表落地透视结果 #

如果业务流程确实需要把透视结果作为独立对象供后续多步查询使用，可以利用临时表的运行时结构特性，在动态SQL执行时把透视结果直接存入临时表，后续静态SQL就可以直接基于这个临时表做关联，不需要提前知道列结构：

-- 动态SQL执行时自动创建结构匹配的临时表，存入透视结果
SET @query = N'
SELECT ProposalID, ' + @cols + N' 
INTO #TempPivotResult
FROM (
  select ProposalID, Amount, ColName 
  from [sbs].[ProposalAmounts]
) x
PIVOT (
  MAX(Amount) for ColName in (' + @cols + N')
) p 
'
EXEC sp_executesql @query;

-- 临时表创建完成后，可直接写常规静态SQL完成关联
SELECT tr.*, t.目标关联字段
FROM #TempPivotResult t
INNER JOIN [sbs].[你的目标关联表] tr
  ON t.ProposalID = tr.ProposalID

注意：以#开头的本地临时表在当前数据库会话全局可见，只要会话未断开、临时表未被手动删除，后续所有静态SQL都可以直接查询关联，无需提前声明表结构。

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