sqlpivot-tabletoad

Keep getting syntax error message in Toad Data Point

I have a table like this:

 Employee ID   Gender
 1             F
 2             F
 3             M
 4             M
 5             M

I just want to pivot it like this:

            M    F
Employee    3    2

The query I wrote is very simple:

SELECT sum(CASE Gender WHEN 'M' THEN 1 ELSE 0 END) M, 
       sum(CASE Gender WHEN 'F' THEN 1 ELSE 0 END) F 

FROM Table

And I always get this message:

[Microsoft][ODBC Excel Driver] Syntax error (missing operator) in query expression 'sum(CASE Gender WHEN 'M' THEN 1 ELSE 0 END) M'.

Anyone knows how to fix it?

Solution

  • CASE is not supported by the ODBC Excel Driver.

    Instead use the function IIF():

    SELECT SUM(IIF(Gender = 'M', 1, 0)) AS M, 
       SUM(IIF(Gender = 'F', 1, 0)) AS F 
FROM tablename;