DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to use the CASE expression to decode a Boolean column?

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

Celebrity Gender BirthDate Age/WouldBeAge
Frank Sinatra Male 12/15/1915 94
Doris Day Female 4/3/1924 85
Marilyn Monroe Female 6/1/1926 83
Elvis Presley Male 1/8/1935 74
Tom Jones Male 6/7/1940 69
Don Henley Male 7/22/1947 62
Madonna Female 9/16/1958 51
Lisa Loeb 3/11/1968 41
Britney Spears Female 12/2/1981 28

 

Related articles:

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

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

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE