conversion error varchar to int in SQL2005

[sap1958]

select attrid,attrcomm,
max(case attrid when 1 then 'comment1' else 0 end) comment1,
max(case attrid when 2 then 'comment2' else 0 end) comment2
from attribute_table
where attrtype = 'hobby'
group by attrid,attrcomm

since attrid is a varchar and not an integer I get the error message Conversion failed when converting the varchar value to data type int

I am trying to show comment1 horizontally next to comment2 so I tried a case statement. So I want to show the distinct attrid with the comments side by side
example
attrid comment1 comment2
1222 hiking fishing

 

[sqlprograms]

Since the field attrid is a varchar value, you might need to include single quote characters when trying to see if the attrid field equals a certain value. For example, in the value below notice the single quote characters surrounding the 1 value.

case attrid when '1' THEN 'comment1' else 0 end

SQL Server Programmers

http://www.sql-programmers.com/

 

[gmmastros]

no. this isn't right.

case attrid when 1 THEN [!]'comment1'[/!] else [!]0[/!] end

This code returns the data in to a single column. The value returned for each row depends on the value in the attrid column. But, what is the data type for this column? Obviously, you think it should be a string, but SQL Server disagrees with you.

There are well defined rules for the resultant data type. To see what I mean, do a google search on "SQL Server data type precedence". In this situation, the resultant data type is int because an int has a higher precedence than varchar. So... SQL is trying to convert "comment1" to an integer and failing miserably.

When using the case/when syntax, it's best to force each branch of execution in to the data type you want. so...

case attrid when '1' THEN 'comment1' else [!]'[/!]0[!]'[/!] end

Without the single quotes, SQL converts 0 to int. With this single quotes, the 0 is converted to a string.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom