SELECT CASE – SQL Server Syntax Example: SELECT CASE – T-SQL Example
SELECT CASE – SQL Server Syntax Example: SELECT CASE – T-SQL Example
Purpose: – Illustrates the SQL Server syntax for the SELECT CASE CLAUSE .
SYNTAX:
Simple CASE expression:
CASE input_expression
WHEN when_expression THEN result_expression [ …n ]
[ ELSE else_result_expression ]
END
Searched CASE expression:
CASE
WHEN Boolean_expression THEN result_expression [ …n ]
[ ELSE else_result_expression ]
END
PURPOSE:
Evaluates a list of conditions and returns one of multiple possible result expressions.
The CASE expression has two formats:
•The simple CASE expression compares an expression to a set of simple expressions to determine the result.
•The searched CASE expression evaluates a set of Boolean expressions to determine the result.
-- SELECT CASE example from http://idealprogrammer.com -- PURPOSE: Evaluates a list of conditions and returns one of multiple possible -- result expressions. -- The CASE expression has two formats: -- •The simple CASE expression compares an expression to a set of simple -- expressions to determine the result. -- •The searched CASE expression evaluates a set of Boolean expressions to -- determine the result. -- SYNTAX: -- Simple CASE expression: -- CASE input_expression -- WHEN when_expression THEN result_expression [ ...n ] -- [ ELSE else_result_expression ] -- END -- Searched CASE expression: -- CASE -- WHEN Boolean_expression THEN result_expression [ ...n ] -- [ ELSE else_result_expression ] -- END CREATE TABLE people( ID int, firstname varchar (20), lastname varchar (20), statecode varchar (2), alive bit, height int ) GO INSERT INTO people (ID, firstname, lastname, statecode, alive, height) VALUES (1, 'Paul', 'Revere', 'AL', 0, 72) GO INSERT INTO people (ID, firstname, lastname, statecode, alive, height) VALUES (2, 'John', 'Lennon', 'NY', 0, 69) GO INSERT INTO people (ID, firstname, lastname, statecode, alive, height) VALUES (3, 'Steve', 'Martin', 'GA', 1, 75) GO INSERT INTO people (ID, firstname, lastname, statecode, alive, height) VALUES (4, 'George', 'Washington', 'MA', 0, 65) GO --Show table without using CASE SELECT * FROM people; GO -- Using AS to combine columns and to change column headings -- Brackets and single quotes can be used when column heading contains spaces SELECT firstname + ' ' + lastname AS [full name], height as 'Height Inches', state = CASE statecode WHEN 'AL' THEN 'Alabama' WHEN 'NY' THEN 'New York' WHEN 'GA' THEN 'Georgia' ELSE 'Unknown' END, living = CASE WHEN alive = 1 THEN 'Living' ELSE 'Dead' END FROM people; GO drop table people; GO |