PIVOT运算符中指定的列名“ irstName”与PIVOT参数中的现有列名冲突

一尘不染

PIVOT运算符中指定的列名“ irstName”与PIVOT参数中的现有列名冲突

sql

我收到的时候我试图做替换以下错误消息null来zero。

PIVOT运算符中指定的列名称“ jan”与PIVOT参数中的现有列名称冲突。

查询如下：

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

阅读 141

2021-05-23

共1个答案

一尘不染

您可以CTE用来定义空值，然后像这样旋转数据：

;WITH t
AS (
    SELECT isnull(jan, 0) AS jan
        ,isnull(feb, 0) AS feb
        ,sum(data) AS amount
    FROM your_table --change this to match your table name
    GROUP BY jan,feb
    )
SELECT *
FROM (
    SELECT t.jan
        ,t.feb
        ,t.amount
    FROM t
    )
pivot(sum(amount) FOR [month] IN (
            jan
            ,feb
            )) AS piv

2021-05-23