基于动态透视表列语句声明表变量

Question

我想声明一个表变量，并用动态列填充它以执行join语句。

DECLARE @cols AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX)
SELECT @cols = 
   STUFF((SELECT DISTINCT ',' + QUOTENAME(ColName)
   FROM [sbs].[ProposalAmounts]
   GROUP BY ColName, ProposalID
   FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')
SET @query = N'SELECT ProposalID, ' + @cols + N' from
   (select ProposalID, Amount, ColName from [sbs].[ProposalAmounts]) x
   PIVOT
   (MAX(Amount)for ColName in (' + @cols + N')) p '
EXEC sp_executesql @query;

这就是我到目前为止所做的事情，我对如何声明其中包含动态列的表变量感到困惑。

这是上面查询的结果：

​

​

这是我想要执行join语句的表的结果：

​

​

Answer 1

就像Jeroen Mostert评论的那样，你需要让一切都变得动态。

我建议将连接放在动态查询中。我使用的不是表变量，而是一个公用表表达式。

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

SELECT @cols = 
   STUFF((SELECT DISTINCT N', ' + QUOTENAME([ColName])
          FROM [_tmp_].[ProposalAmounts]
          GROUP BY [ColName], [ProposalID]
          FOR XML PATH(N''), TYPE).value(N'.', N'NVARCHAR(MAX)'), 1, 2, N'')

SET @query = N'
WITH [CTE_Pivoted_ProposalAmounts] AS
(
  SELECT [ProposalID], ' + @cols + N'
  FROM
    (SELECT [ProposalID], [Amount], [ColName] FROM [sbs].[ProposalAmounts]) x
    PIVOT (MAX([Amount]) FOR [ColName] IN (' + @cols + N')) p
)
SELECT *
FROM
  [sbs].[OtherTable] ot
  INNER JOIN [CTE_Pivoted_ProposalAmounts] ppa ON ppa.[ProposalID] = ot.[ProposalID];
';

EXEC sp_executesql @query;

您需要将[sbs].[OtherTable]替换为要连接的表的实际名称。您还可以调整联接条件和SELECT子句中的字段。这里的代码只是一个简单的例子。我假设您会设法自己修复查询，使其行为符合您的期望。