SQL服务器:透视功能错误
问题描述:
,我发现了以下错误:SQL服务器:透视功能错误
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '('.
当执行此查询:
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;
我试图同时使用YEAR
和DATEPART
。错误是引用第二个DATEPART
上的开头括号。
答
你需要一个别名分配给DATEPART表达和使用您的数据透视子句中:
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;
答
你不需要Pivot
做到这一点。试试这种方式
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'
+0
只要我用null替换0就行。谢谢,这个解决方案更容易理解。 – gattoun
+0
@gattoun - 我的不好..更新了我们不需要'else'部分 –
你打我吧。 – Eli