SQL PIVOT Syntax Error on Aggregate
I'm getting a very unexpected error, and I'm not sure if it is due to compatibility settings or something silly. (I'm using SQL Server 2008 R2.) My query fails on the aggregate inside the pivot.
Here is the full sample code.
Snippet:
开发者_StackOverflow社区select * from @sales
pivot
(
sum(Amount)
for Quarter
in (Q1, Q2, Q3, Q4)
) as p
Incorrent syntax on the line sum(Amount)
Works for me for table definition
declare @sales table
(
[Year] int,
Quarter char(2),
Amount float
)
so presumably it is a compatibility level issue. You can do
SELECT
[Year],
SUM(CASE WHEN Quarter = 'Q1' THEN Amount END) AS Q1,
SUM(CASE WHEN Quarter = 'Q2' THEN Amount END) AS Q2,
SUM(CASE WHEN Quarter = 'Q3' THEN Amount END) AS Q3,
SUM(CASE WHEN Quarter = 'Q4' THEN Amount END) AS Q4
from @sales
group by [Year]
For a solution that will work under SQL Server 2000 compatibility mode.
精彩评论