title: Using Declared Variables in a Subquery
body: <p>The Following query is "supposed to" use the subquery to find items in a specific column, and pivot them so that each of those items becomes their own column. The subquery works fine on its own, but when I try to add it to a larger query that would then take the results of the subquery and insert them into a table, I start getting a couple syntax errors. Below is the entire query:</p>
  
  <pre><code>DECLARE @cols AS NVARCHAR(MAX);
  DECLARE @query AS NVARCHAR(MAX);
  
  Insert Into tbl_AS ( Billing_Account_ID, Bill_Date, MTN, User_Name  ,Adjustment_To_Balance_Previous_Description,Adjustments_To_Previous_Balance
      ,Adjustment_Type)
  
  Select
      As_Selection.Billing_Account_ID
      ,As_Selection.Bill_Date
      ,As_Selection.MTN
      ,As_Selection.User_Name
      ,As_Selection.Adjustment_To_Balance_Previous_Description
      ,As_Selection.Adjustments_To_Previous_Balance
      ,As_Selection.Adjustment_Type
  
  
      FROM( --Subquery in question
  
  SELECT @cols = STUFF((SELECT distinct 
                          ',' +
                          QUOTENAME([Section_1])
                  FROM [dbo].[Import_tbl_RDO]
                  where [dbo].[Import_tbl_RDO].[Section_1] &lt;&gt; 'Prior Activity' AND [dbo].[1_Import_tbl_RDO].[Section_1] &lt;&gt; 'Group Details' AND [dbo].[Import_tbl_RDO].[Section_1] &lt;&gt; 'Wireless Line Summary' AND  [dbo].[Import_tbl_RDO].[Section_1] &lt;&gt; 'Wireless Details' AND  [dbo].[Import_tbl_RDO].[Section_1] &lt;&gt; 'Equipment &amp; Accessories Charges'
              FOR XML PATH(''), TYPE 
              ).value('.', 'NVARCHAR(MAX)') 
          ,1,1,'')
  
  Set @query = ' Select [Billing Account Number], [Period End Date], [Wireless Number], [User Name]' + @cols +'
      From 
          (
              Select 
                   [Amount] as SumOfAmount,[Billing Account Number], [Period End Date],  [Wireless Number], [User Name]
                   From [dbo].[Import_tbl_RDO]
                   Where [dbo].[Import_tbl_RDO].[Section ID] = ''IRA'' AND [dbo].[Import_tbl_RDO].[Section_1] IS Not Null
                   ) t
                   PIVOT 
                   (
                   SUM(SumOfAmount)
                      FOR [Section_1] IN(' + @cols + ')
                      ) p '
  
      Execute (@query)
  
      ) as AS_Selection
  </code></pre>
  
  <p>The errors are showing up as Incorrect Syntax at <code>@cols = STUFF</code> and <code>FOR XML</code>. Am I way off in how this subquery needs to be written?</p>
  
  <p>EDIT:</p>
  
  <p>Following the suggestion from below, I have reordered my query to look like this:</p>
  
  <pre><code>DECLARE @cols AS NVARCHAR(MAX);
  DECLARE @query AS NVARCHAR(MAX);
  
  SELECT @cols = STUFF((SELECT distinct 
                          ',' +
                          QUOTENAME([Section_1])
                  FROM [dbo].[Import_tbl_RDO]
                  where [dbo].[Import_tbl_RDO].[Section_1] &lt;&gt; 'Prior Activity' AND [dbo].[1_Import_tbl_RDO].[Section_1] &lt;&gt; 'Group Details' AND [dbo].[Import_tbl_RDO].[Section_1] &lt;&gt; 'Wireless Line Summary' AND  [dbo].[Import_tbl_RDO].[Section_1] &lt;&gt; 'Wireless Details' AND  [dbo].[Import_tbl_RDO].[Section_1] &lt;&gt; 'Equipment &amp; Accessories Charges'
              FOR XML PATH(''), TYPE 
              ).value('.', 'NVARCHAR(MAX)') 
          ,1,1,'')
  
  Set @query = 'INSERT INTO tbl_AS (
      Billing_Account_ID
      ,Bill_Date
      ,MTN
      ,User_Name
      ,As_Selection.Adjustment_To_Balance_Previous_Description
      ,As_Selection.Adjustments_To_Previous_Balance
      ,As_Selection.Adjustment_Type
      )
  
  
              Select
                  As_Selection.[Billing Account Number]
                  ,As_Selection.[Period End Date]
                  ,As_Selection.[Wireless Number]
                  ,As_Selection.[User Name]
                  ,As_Selection.Adjustment_To_Balance_Previous_Description
                  ,As_Selection.Adjustments_To_Previous_Balance
                  ,As_Selection.Adjustment_Type
  
  
                  FROM( 
  
  
                      Select [Billing Account Number], [Period End Date], [Wireless Number], [User Name]' + @cols +'
                          From 
                              (
                                  Select 
                                       [Amount] as SumOfAmount,[Billing Account Number], [Period End Date],  [Wireless Number], [User Name]
                                       From [dbo].[Import_tbl_RDO]
                                       Where [dbo].[Import_tbl_RDO].[Section ID] = ''IRA'' AND [dbo].[Import_tbl_RDO].[Section_1] IS Not Null
                                       ) t
                                       PIVOT 
                                       (
                                       SUM(SumOfAmount)
                                          FOR [Section_1] IN(' + @cols + ')
                                          ) p 
                          ) as AS_Selection'
                  Execute (@query)
  </code></pre>
  
  <p>While this seems to have gotten me closer, I am still running into issues. Now the only error I get is one telling me that the Column <code>User Name</code> does not exist. I know the column exists, so I think this is just an error being thrown by something else, potentially the <code>'+@cols+'</code>. Any ideas?</p>
  
  <p>EDIT:</p>
  
  <p>I have Printed the values for both @query and @cols. The Results for @query are the same as the edited query I posted a few minutes ago, except the print did not show the Declare or print statements. The @cols Print looked like this:</p>
  
  <p><code>[Adjustment To Balance Previous Description], [Adjustments To Previous Balance], [Adjustment Type]</code></p>