Hi,
I'm having a problem while writing cross tab queries. The fact is I'm getting errors when I execute the query.
The query is as follows.

SELECT skill_Id,
count(CASE final_rating WHEN 5 THEN assoc_name ELSE 0 END) AS "5",
count(CASE final_rating WHEN 4 THEN assoc_name ELSE 0 END) AS "4",
count(CASE final_rating WHEN 3 THEN assoc_name ELSE 0 END) AS "3",
count(CASE final_rating WHEN 2 THEN assoc_name ELSE 0 END) AS "2"
FROM viewfinalrating
GROUP BY skill_id

My purpose is to get the count of the assoc_name for each final_rating grouped by skill_id. When I execute the above query I'm getting the error

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'Name1' to a column of data type int.

Name1 is an assoc_name in the view viewfinalrating. Why is SQL server trying to convert the assoc_name to int. Isn't the query supposed to get the count of assoc name based on the CASE statements?

What could be the problem?

Thanks in advance
Regards,
P.C. Vaidyanathan

When you use CASE statments you must return like data types. You need to change the "ELSE 0" to a "ELSE '0'" or "Null". However if you change you approach just a bit I think you can achive your goal...

Try
--------------------------------------------------------------------------------------------------
SELECT skill_Id,
sum(CASE final_rating WHEN 5 THEN 1 ELSE 0 END) AS "5",
sum(CASE final_rating WHEN 4 THEN 1 ELSE 0 END) AS "4",
sum(CASE final_rating WHEN 3 THEN 1 ELSE 0 END) AS "3",
sum(CASE final_rating WHEN 2 THEN 1 ELSE 0 END) AS "2"
FROM viewfinalrating
GROUP BY skill_id
--------------------------------------------------------------------------------------------