title: dynamic sql pivot in sql server
body: <p>Running this query in sql server 2008 now, but soon plan to move it in creating a report in sql reporting services: </p>
  
  <pre><code>SELECT * from ( SELECT Amount, Year, column1, column2,column3 from BUYSCTE ) BUY 
  </code></pre>
  
  <p>My table results running above query without the pivot is this</p>
  
  <pre><code>Column1 | Column2 | Column3| FYYear| Amount|
  
   1           cat     dog      2011   50 
   1           cat     dog      2012   75
   1           cat     dog      2013   65
   2          fish     snake    2011   23
   2          fish     snake    2012   39
   2          fish     snake    2013   59
   .. 
   ..  
   ..                           2016
  </code></pre>
  
  <p>so basically I want the results to end up like this:</p>
  
  <pre><code>Column1 | Column2 | Column3| 2011| 2012 | 2013 
   1           cat     dog      50    75     65
   2          fish     snake    23    39     59
  </code></pre>
  
  <p>so the query I came up with to make this happen is I first created a table variable and made it a dynamic variable and did a pivot like this,</p>
  
  <pre><code>declare @Year nvarchar(Max)
  set @Year = STUFF(
  (SELECT ', ' + quotename(FYYear)
  from BUYSCTE Group By FYYear order by
  FYYear For XML PATH(''))
  , 1, 2, '');
  </code></pre>
  
  <p>The above populates the @Year value with: [2011], [2012], [2013], [2014], [2015], [2016]</p>
  
  <p>I know that because I change the above select to  SELECT  @Year just to check if the value is set correctly</p>
  
  <p>from there  I change the above select after the table variable to</p>
  
  <pre><code>SELECT * from ( SELECT Amount, FYYear, column1, column2,column3 from BUYSCTE ) BUY 
  PIVOT( SUM(Amount) FOR FYYear in ([@Year]) ) pvt
  </code></pre>
  
  <p>but my results end up being this</p>
  
  <pre><code> Column1 | Column2 | Column3| @Year|
   1           cat     dog      null   
   2          fish     snake    null
  </code></pre>
  
  <p>What am I doing wrong? it seems like something small I am missing that I cannot see myself. even if I change the aggregate function from SUM to COUNT it gives 0 for the @Year column values instead of null</p>