title: T-SQL pivot where clause
body: <p>in SQL Server i want PIVOT a table and add a WHERE clause but i cant figure out the syntax. </p>
  
  <h1>The data</h1>
  
  <blockquote>
  <pre><code>dbo.SOME_VIEW
  YEAR  AMOUNT
  ====================
  2014  1
  2013  2
  2012  5.6
  2011  574
  2010  123
  </code></pre>
  </blockquote>
  
  <h1>The Query</h1>
  
  <pre><code>SELECT
      *
  FROM SOME_VIEW
  PIVOT (SUM(AMOUNT) FOR [YEAR] IN ([2012], [2013], [2014])) AS PIVOTED
  </code></pre>
  
  <p>Now i want to get from the view only the row with the year that i will PIVOT so i want to add </p>
  
  <p><code>WHERE YEAR IN (2012, 2013, 2014)</code></p>
  
  <h1>What i have tried</h1>
  
  <p>Put <code>WHERE</code> after <code>FROM SOME_VIEW</code></p>
  
  <pre><code>SELECT
      *
  FROM SOME_VIEW WHERE YEAR IN (2012, 2013, 2014)
  PIVOT (SUM(AMOUNT) FOR [YEAR] IN ([2012], [2013], [2014])) AS PIVOTED
  </code></pre>
  
  <p>i get: <code>Incorrect syntax near the keyword 'PIVOT'</code>. When i add the <code>WHERE</code> at the end end i get <code>Invalid column name 'YEAR'</code>.</p>
  
  <h1>Question</h1>
  
  <p>Where can i add the WHERE clause when i use PIVOT?</p>
  
  <h1>Why</h1>
  
  <p>For performance. In the view i have years from 1990 but i want only the last thre years. I expect when i add a where i improve performance.</p>