Convert bit type to Yes or No by query Sql Server

问题:

I Want convert bit type to Yes or No

For Example:

SELECT FirstName, LastName, IsMale from members

Results:

Ramy    Said    1  

Expected Result:

Ramy    Said    Yes  

回答1:

You can do this using a searched case expression:

SELECT
    FirstName,
    LastName,
    CASE WHEN IsMale = 1 THEN 'Yes' ELSE 'No' END AS IsMale
FROM
    Members

回答2:

Here you go:

SELECT FirstName, LastName, CASE WHEN IsMale = 1 THEN 'Yes' ELSE 'No' END AS Male
FROM members

Basically, you use a CASE statement to allow you to convert the value. If you had three choices, you could still use the case statement and just add another option (obviously you can't have three options with a bit but if the field was an int, etc.) The ELSE statement is the default statement that runs if you don't get a match. In our case, we just use it for No since we can only have yes or no but in the case of a larger CASE statement, you would want to use this as your fall-back field. For example, you could say "Item Not Found" if you were converting items.

回答3:

Use a CASE;

SELECT 
  FirstName,
  LastName,
  CASE IsMale WHEN 1 THEN 'Yes' ELSE 'No' END AS IsMale
FROM tbl

回答4:

Use an IIF() which is available SQL Server 2012 onwards.

There is one another way of achieving this. (shorthand way(maybe not)). That's using IIF(). See below.

SELECT
  [FirstName],
  [LastName],
  IIF([IsMale]=1,'Yes','No') AS [IsMale As String] 
FROM 
  [Members]

回答5:

SQL Server 2012 introduces two new keywords FORMAT and IIF that can provide a more compact means of converting an integer or bit to string:

DECLARE @MyInt int = 123
PRINT 'Hello '+FORMAT(@MyInt,'')

-- (note: the "+0" converts a bit type to int then outputs '0' or '1')
DECLARE @MyBit bit = 0
PRINT 'Hello '+FORMAT(@MyBit+0,'')

But to convert a bit type to Yes or No I would now use:

PRINT 'Hello '+IIF(@MyBit=1,'Yes','No')