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.

TT. · Accepted Answer · 2017-07-31 15:58:56Z

3

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;

answered Jul 31, 2017 at 15:58

TT.

16.2k6 gold badges53 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Eli Over a year ago

you beat me to it.

Pரதீப் · Accepted Answer · 2017-07-31 16:13:20Z

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'

edited Jul 31, 2017 at 16:13

answered Jul 31, 2017 at 16:01

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

2 Comments

gattoun Over a year ago

This worked as long as I replaced the 0 with null. Thank you, this solution is much easier to understand.

Pரதீப் Over a year ago

@gattoun - My bad.. updated we don't need else part

Collectives™ on Stack Overflow

SQL Server : Pivot Function Error

2 Answers 2

1 Comment

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Related