Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2002
    Posts
    28

    Unanswered: Cross Tab Query giving erros

    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

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    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
    --------------------------------------------------------------------------------------------
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •