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

Question

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

@ManoJohnbritto - please *[edit]* your question to add sample data and expected results — Damien_The_Unbeliever, Jun 22 '15 at 13:58

Christian Barron · Answer 1 · 2015-06-22T14:00:45.273

3

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

edited Jun 22 '15 at 14:00

answered Jun 22 '15 at 13:40

Christian Barron

2,695
1
14
22

I think the use of "month" is causing chaos as that is a reserved word. Is it a column in your table? – Christian Barron Jun 22 '15 at 13:48
You need to select "month" in the first select too or whatever you have renamed month to. – Christian Barron Jun 22 '15 at 13:56
You have any idea replace null to zero on this query – Mano Johnbritto Jun 22 '15 at 14:00
I've updated my code to what should work but this assumes [month] will contain "jan" or "feb" if this is not what you want you'll need to update your question with what your expected results are. – Christian Barron Jun 22 '15 at 14:02
Thanku so much Barron, – Mano Johnbritto Jun 22 '15 at 14:06

FutbolFan · Accepted Answer · 2015-06-22T14:57:20.443

1

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

edited Jun 22 '15 at 14:57

answered Jun 22 '15 at 14:01

FutbolFan

13,235
3
23
35

Thanku Nepali,Why using with – Mano Johnbritto Jun 22 '15 at 14:21
`WITH` clause helps you build a query and do all your null logic(in your case), which then can be used in your second part of the query without having to do any type of data manipulation. If you are interested, here is more information on using `CTE` in sql server. https://www.simple-talk.com/sql/t-sql-programming/sql-server-cte-basics/ – FutbolFan Jun 22 '15 at 14:24

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

2 Answers2

Linked