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

I know exactly where is your problem, since I am jut facing it right now. The WHERE Clause and the PIVOT Clause don't like each other. So, PIVOT cannot come Directly after WHERE, unless there is a barrier between them. This say, you MUST wrap your SELECT Clause as a Subquery of another SELECT Clause, like this:

SELECT * FROM

(

SELECT * FROM SOME_VIEW WHERE YEAR IN (2012, 2013, 2014)

)

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

WITH COMMENTS

SELECT * FROM --Master Query you should add

(

SELECT * -- This is the query you had before

FROM SOME_VIEW

WHERE YEAR IN (2012, 2013, 2014)

) -- End of the Master Query. This is the barrier between the WHERE and the PIVOT. After this parenthesis, you can now safely call you PIVOT Clause.

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

Answer 2

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: