SQL Server : Pivot Function Error

Question

I'm getting the following error:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '('.

When executing this query:

SELECT 
    'Average Total Comp' AS AverageTotalComp, 
    [2016], [2015], [2014] 
FROM 
    (SELECT 
         DATEPART(yyyy, [Fiscal Year End Date]), 
         [Total Compensation ($)] 
     FROM 
         dbo.MDexec e) AS SourceTable
PIVOT 
    (AVG([Total Compensation ($)]) 
     FOR DATEPART(yyyy, [Fiscal Year End Date])  
         IN ([2016], [2015], [2014])) AS PivotTable;

I attempted to use both YEAR and DATEPART. The error is referencing the opening parentheses on the second DATEPART.

Answer 1

You don't need Pivot to do this. Try this way

SELECT AverageTotalComp = 'Average Total Comp',
       [2016] = Avg(case when year([Fiscal Year End Date]) = 2016 then [Total Compensation ($)] end), 
       [2017] = Avg(case when year([Fiscal Year End Date]) = 2017 then [Total Compensation ($)] end), 
       [2018] = Avg(case when year([Fiscal Year End Date]) = 2018 then [Total Compensation ($)] end)
FROM dbo.MDexec e
Where [Fiscal Year End Date] >= '2015-01-01' 
  and [Fiscal Year End Date] < '2019-01-01'

Answer 2

You need to assign an alias to the datepart expression and use that in your pivot clause:

SELECT 'Average Total Comp' AS AverageTotalComp, 
[2016], [2015], [2014] 
FROM (SELECT datepart(yyyy,[Fiscal Year End Date]) AS dp, 
[Total Compensation ($)] FROM dbo.MDexec e) 
AS SourceTable
PIVOT (
avg([Total Compensation ($)]) 
FOR dp 
IN ([2016], [2015], [2014])) AS PivotTable;