sql - Declare a Table Variable based on dynamic pivot columns statement

Question

I want to declare a table variable and fill it from the pivot with dynamic column to perform join statement.

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;

This is what I've done so far and I'm confused as to how to declare a table variable that has dynamic columns in it.

This is the result of the query above:

And this is the result of the table I want to perform join statement:

question from:https://stackoverflow.com/questions/65933320/declare-a-table-variable-based-on-dynamic-pivot-columns-statement

Answer 1

Like Jeroen Mostert commented, you need to make everything dynamic.

I would suggest to put the join inside the dynamic query. Instead of a table variable, I use a common table expression.

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;

You need to replace [sbs].[OtherTable] with the actual name of the table you want to join with. And you might also tweak the join criteria and the fields in the SELECT clause. This code here is just a simple example. I assume you will manage to fix the query yourself to make it behave as you expect.