sql - PIVOT 运算符中指定的列名 "FirstName"与 PIVOT 参数中的现有列名冲突

Question

当我尝试将 null 替换为 zero 时，收到以下错误消息。

The column name "jan" specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument.

查询如下:

select * from(select isnull(jan,0),isnull(feb,0),sum(data) as amount )as p
pivot(
sum(amount) for month in(jan,feb)) as piv

Answer 1

这就像错误表明您的列名称错误，因为它们将被读取为未命名。给他们合适的名字:

select isnull(Jan,0), isnull(feb,0) from(select sum(data) as amount, [month] 
from yourtable group by [month] )as p
pivot(
sum(amount) for [month] in(jan,feb)) as piv