T-SQL pivot where clause

Question

in SQL Server i want PIVOT a table and add a WHERE clause but i cant figure out the syntax.

The data

dbo.SOME_VIEW
YEAR  AMOUNT
====================
2014  1
2013  2
2012  5.6
2011  574
2010  123

The Query

SELECT
    *
FROM SOME_VIEW
PIVOT (SUM(AMOUNT) FOR [YEAR] IN ([2012], [2013], [2014])) AS PIVOTED

Now i want to get from the view only the row with the year that i will PIVOT so i want to add

WHERE YEAR IN (2012, 2013, 2014)

What i have tried

Put WHERE after FROM SOME_VIEW

SELECT
    *
FROM SOME_VIEW WHERE YEAR IN (2012, 2013, 2014)
PIVOT (SUM(AMOUNT) FOR [YEAR] IN ([2012], [2013], [2014])) AS PIVOTED

i get: Incorrect syntax near the keyword 'PIVOT'. When i add the WHERE at the end end i get Invalid column name 'YEAR'.

Question

Where can i add the WHERE clause when i use PIVOT?

Why

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.

Answer 1

As I understand it you want to restrict the years that are passed on to the PIVOT.

You can do this by using a Common Table Expression.

This should work for you:

;WITH CTE AS (SELECT
 *
FROM #SOME_VIEW 
WHERE [YEAR] IN (2012, 2013, 2014))

SELECT
 *
FROM CTE
PIVOT (SUM(AMOUNT) FOR [YEAR] IN ([2012], [2013], [2014])) AS PIVOTED 

RESULTS: