将列在SQL字符串选择(Convert column to string in SQL Select

我期待一个列转换为字符串，其中列是选择statment然后CONCAT与另一列。 这是使用CONVERT或CAST发生时，我的困惑。

例：

 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

现在，这个查询的工作完美的是提供登录的正确数量和失败为整数。 不过，我想用这些整数作为字符串，所以我可以一列。 还有一个原因，这就需要将一列作为字符串。

我想只有4列，而不是5登录专栏中，我想有被loginCountFailed / LoginCount。 例如：3/12。 我需要的是一个字符串，因为你不能用0分，并有多次分母为0。

Answer 1:

对于MSSQL-2005，你应该使用CAST concatanating号

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

loginsFailed和loginCount上面实际上是你的select count distinct片段

我希望这个工程

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))

Answer 2:

DECLARE @i int
SET @i=98235

--Method 1 : Use CAST function
SELECT CAST(@i as varchar(10))

--Method 2 : Use CONVERT function
SELECT CONVERT(varchar(10),@i)

--Method 3 : Use STR function
SELECT LTRIM(STR(@i,10))

资源

Answer 3:

你可以通过以下CAST或CONVERT ：

CONVERT(VARCHAR(20), YourIntColumn)

要么

CAST(YourIntColumn AS VARCHAR(20))

Answer 4:

你想要做的是有一个case语句被零逻辑来处理鸿沟，无法切换到对于数字数据的字符串。

SELECT employeeID
    ,name
    ,location
    ,(SELECT CASE WHEN COUNT(DISTINCT logins) = 0 then 0 ELSE
COUNT(DISTINCT loginsFailed) / COUNT(DISTINCT logins)
END
     FROM users
     WHERE (users.employeedID = userDetails.employeeID)
        AND (users.startdate = 01-01-2013) ) as LoginFailRatio
 FROM userDetails

Answer 5:

我认为这将这样的伎俩

Select column1 + '/' + column2 from table1

Answer 6:

我想你可以就在这里建立一个外部查询：

SELECT u.employeeID, u.name, u.location,  
       CONVERT(varchar(10), u.LoginCountFailed) + '/' + CONVERT(varchar(10), u.LoginCount) "Ratio" 
    FROM
        (
         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
        ) u

文章来源: Convert column to string in SQL Select