SELECT CASE – SQL Server Syntax Example: SELECT CASE – T-SQL Example

Get 5 Hours of FREE PREMIUM Videos:

LearnVisualStudio.NET Free Preview

LearnVisualStudio Premium Videos

dotNetVideos Premium Video Catalog

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.

 T-SQL | 
 copy code |
?
 
-- 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
 
 

Sample Output for SELECT CASE Example

Related posts:

1. SELECT ALL SQL Server Syntax Example: SELECT ALL argument T-SQL Example SELECT ALL SQL Server Example: SELECT ALL argument T-SQL Example...
2. BETWEEN AND – SQL Server Syntax Example: BETWEEN AND – T-SQL Example BETWEEN AND - SQL Server Syntax Example: BETWEEN AND -...
3. Column AS Alias SQL Server Syntax Example: Column AS T-SQL Example SELECT AS SQL Server Syntax Example: SELECT AS T-SQL Example...
4. SELECT AND SQL Server Syntax Example: SELECT AND logical operator T-SQL Example SELECT AND SQL Server Syntax Example: SELECT AND logical operator...
5. Subquery ALL SQL Server Example: Subquery ALL modifier Example Subquery ALL SQL Server Example: Subquery ALL modifier Example...

Related posts brought to you by Yet Another Related Posts Plugin.