cast problem -- int to varchar

[SQL Apprentice]

Hello,

I have the following cross tab query...I need to convert the EmployeeStatus
from an int to a varchar datatype so I can describe the result more clearly
rather than just 0,1,2.
I would like the result to say "new hired" instead of 1.
I tried to cast around the SUM function but I got a conversion error.

SELECT
SUM(CASE EmployeeStatus
WHEN 1
THEN 'new hired'
ELSE 'current'
END) AS EmployeeStatus,
EmployeeID
FROM Employee
WHERE StatusDate > '2003/07/01'
GROUP BY EmployeeID

Any suggestions???

Thanks a bunch

[Aaron Bertrand - MVP]

Your result inside the CASE looks like this:

'new hired'
'new hired'
'current'
'new hired'

How do you propose to do a SUM() on those?

Did you mean:

SELECT EmployeeID, EmployeeStatus = CASE EmployeeStatus
WHEN 1 THEN 'new hired' ELSE 'current' END
FROM Employee WHERE StatusDate >= '20030701'



"SQL Apprentice" <mssqlworld@yahoo.com> wrote in message
news:eP$$MBxRDHA.560@TK2MSFTNGP10.phx.gbl...

> Hello,
>
> I have the following cross tab query...I need to convert the

EmployeeStatus

> from an int to a varchar datatype so I can describe the result more

clearly

> rather than just 0,1,2.
> I would like the result to say "new hired" instead of 1.
> I tried to cast around the SUM function but I got a conversion error.
>
> SELECT
> SUM(CASE EmployeeStatus
> WHEN 1
> THEN 'new hired'
> ELSE 'current'
> END) AS EmployeeStatus,
> EmployeeID
> FROM Employee
> WHERE StatusDate > '2003/07/01'
> GROUP BY EmployeeID
>
> Any suggestions???
>
> Thanks a bunch
>
>