As my experience, Pivot function best works on CTEs and subqueries from which the result sets do not have primary keys. Take a look at the following Example:

The schema of the Employee table is:

SELECT IT, ACCOUNT, SALES FROM [dbo].[Employee] pivot( MAX([salary]) for [dept_id] in (IT, ACCOUNT, SALES) ) as cnt;

WITH ProjectdEmployees as (SELECT dept_id, salary from [dbo].[Employee])

SELECT IT, ACCOUNT, SALES FROM ProjectdEmployees pivot( MIN ([salary]) for [dept_id] in (IT, ACCOUNT, SALES) ) as cnt;