Convert column to string in SQL Select

Question

I am looking to convert a column to string where the column is a select statment and then concat with another column. This is where my confusion occurs when using CONVERT or CAST.

Example:

 SELECT employeeID
    ,name
    ,location
    ,(SELECT COUNT(DISTINCT loginsFailed)
     FROM users
     WHERE (users.employeedID = userDetails.employeeID)
        AND (users.startdate = 01-01-2013) as LoginCountFailed
  ,(SELECT COUNT(DISTINCT logins)
   FROM users
   WHERE (users.employeedID = userDetails.employeeID)
       AND (users.startdate = 01-01-2013) as LoginCount
 FROM userDetails

Now, this query works perfect in that is provides the correct number of logins and failed as integers. However, I want to use these integer as a string so i can one column. There is a reason why this needs to be one column as string.

I want to have only 4 columns, not 5. The login column I want to have is loginCountFailed/LoginCount. For example: 3/12. I need it as a string because you cannot divide by a 0 and there are times where the denominator is 0.

Answer 1

You can do the following by using CAST or CONVERT:

CONVERT(VARCHAR(20), YourIntColumn)

OR

CAST(YourIntColumn AS VARCHAR(20))

Answer 2

For concatanating numbers in MSSQL-2005 you should use CAST

CAST(loginsFailed AS VARCHAR(10)) + '/' + CAST(LoginCount AS VARCHAR(10))

loginsFailed and loginCount above is actually your select count distinct fragments

I hope that this works

CAST ((SELECT COUNT(DISTINCT loginsFailed) FROM users WHERE users.employeedID = userDetails.employeeID AND users.startdate = 01-01-2013) AS VARCHAR(10))
+ '/' +
CAST ((SELECT COUNT(DISTINCT logins) FROM users WHERE users.employeedID = userDetails.employeeID AND users.startdate = 01-01-2013) AS VARCHAR(10))