Cross Tab Query giving error

[vaidyanathanpc]

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

 

[bperry]

I'm not really picking up on what you're trying to do. (my fault, not yours.) But here's 3 other versions that are easy to try, so why not see if one of them does it for you.
-------------------
SELECT skill_Id,
count(CASE final_rating WHEN 5 THEN 1 ELSE 0 END) AS "5",
count(CASE final_rating WHEN 4 THEN 1 ELSE 0 END) AS "4",
count(CASE final_rating WHEN 3 THEN 1 ELSE 0 END) AS "3",
count(CASE final_rating WHEN 2 THEN 1 ELSE 0 END) AS "2"
FROM viewfinalrating
GROUP BY skill_id
-------------------
SELECT skill_Id,
SUM(CASE final_rating WHEN 5 THEN assoc_name ELSE 0 END) AS "5",
SUM(CASE final_rating WHEN 4 THEN assoc_name ELSE 0 END) AS "4",
SUM(CASE final_rating WHEN 3 THEN assoc_name ELSE 0 END) AS "3",
SUM(CASE final_rating WHEN 2 THEN assoc_name ELSE 0 END) AS "2"
FROM viewfinalrating
GROUP BY skill_id
-------------------
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
-------------------

 

[arrowhouse]

The trouble is your case statements:

CASE final_rating WHEN 5 THEN assoc_name ELSE 0 END

assoc_name is a varchar & 0 is an int. You need to have them the same type or convert them so they are the same type

I'd go for

CASE final_rating WHEN 5 THEN 1 ELSE 0 END & use sum rather than count ie the last of bperry's suggestions.