CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Mano Johnbritto
Mano Johnbritto

Reputation: 308

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

I am getting the following error message when I am trying to do replace null to zero.

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

Query below:

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

Upvotes: 6

Views: 9789

Answers (2)

FutbolFan
FutbolFan

Reputation: 13743

You could use CTE to define your null values and then pivot the data something like this:

;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

Upvotes: 1

Christian Barron
Christian Barron

Reputation: 2755

It's like the error says your column names are wrong because they will be read as un-named. Give them their appropriate names:

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

Upvotes: 3

Related Questions