The type of column “xxx” conflicts with the type of other columns specified in the UNPIVOT list.

When pivoting data in SQL Server, all columns must be of same time, otherwise you will get this error. You might get this error if you have imperfect case statement or one column is of integer type while the other is of type varchar.

Interesting I was getting this error even after casting my columns into varchar(10) but still the error. Turned out just casting wouldn’t fix the problem either. My problem code was

cast(Total  as varchar(10)) as Total,

cast (cast(Available as float)/(cast(Total as float) as varchar(10)) + '%' as PercentValue

 

The above code was giving The type of column “PercentValue” conflicts with the type of other columns specified in the UNPIVOT list becaust i was unpivoting like this

UNPIVOT(

        [Count] for YesNO in ([Total],[PercentValue])

) B

 

In the above query  + ‘%’ was the one that gave me the problem. By appending the string ‘%’ with varchar(10), it  return a true varchar type but my first string was only casted value. So adding + ‘ ‘ to the first string fixed the problem, which is basically a lank string. So the fixed code looked like this

cast(Total  as varchar(10)) + ' ' as Total,

cast (cast(Available as float)/(cast(Total as float) as varchar(10)) + '%' as PercentValue

which is merely adding +’ ‘ to string value.

Advertisements
This entry was posted in SQL Server 2008 and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s