Execute the following SQL Server T-SQL sample scripts in Management Studio Query Editor to illustrate the applicaton of the CASE function to translate Boolean data (0,1) to words.

-- SQL case function as translate function

USE AdventureWorks;

GO

-- Create table for demo

CREATE TABLE #Celebrity (

  [ID]      INT    IDENTITY ( 1 , 1 )    PRIMARY KEY,

  [Name]    VARCHAR(64)    NOT NULL UNIQUE,

  Gender    BIT    NULL, -- Boolean 1:male 0:female

  BirthDate SMALLDATETIME    NOT NULL,

  ModifiedDate datetime default (getdate()))

GO

-- Populate Celebrity table

-- SQL insert - insert into

INSERT #Celebrity ([Name], Gender, BirthDate) VALUES ('Elvis Presley', 1, '1935-01-08')

INSERT #Celebrity ([Name], Gender, BirthDate) VALUES ('Marilyn Monroe', 0, '1926-06-01')

INSERT #Celebrity ([Name], Gender, BirthDate) VALUES ('Doris Day', 0, '1924-04-03')

INSERT #Celebrity ([Name], Gender, BirthDate) VALUES ('Britney Spears', 0, '1981-12-02')

INSERT #Celebrity ([Name], Gender, BirthDate) VALUES ('Frank Sinatra', 1, '1915-12-15')

INSERT #Celebrity ([Name], Gender, BirthDate) VALUES ('Lisa Loeb', null, '1968-03-11')

INSERT #Celebrity ([Name], Gender, BirthDate) VALUES ('Don Henley', 1, '1947-07-22')

INSERT #Celebrity ([Name], Gender, BirthDate) VALUES ('Madonna', 0, '1958-09-16')

INSERT #Celebrity ([Name], Gender, BirthDate) VALUES ('Tom Jones', 1, '1940-06-07')

GO

/* Error message upon duplicate [Name] insert attempt:

Msg 2627, Level 14, State 1, Line 6

Violation of UNIQUE KEY constraint 'UQ__#Celebri__737584F600200768'. Cannot insert

duplicate key in object 'dbo.#Celebrity'.

*/

-- SQL CASE function to decode a Boolean column

SELECT   Celebrity = [Name],

         Gender = CASE Gender

                    WHEN 0 THEN 'Female'

                    WHEN 1 THEN 'Male'

                    ELSE ''

                  END,

         BirthDate = CONVERT(VARCHAR,BirthDate,107),

         [Age/WouldBeAge] = DATEDIFF(YEAR,BirthDate,getdate())

FROM     #Celebrity

ORDER BY [Age/WouldBeAge] DESC

GO

DROP TABLE #Celebrity

GO

-- Results

Related articles:

http://www.sqlusa.com/bestpractices/training/scripts/casefunction/

In T-SQL, use CASE...WHEN in place of IF...THEN