SQL - CHOOSE()

Simple CHOOSE Example

returns the third item from the list of values that is provided.

SELECT CHOOSE ( 3, 'Manager', 'Director', 'Developer', 'Tester' ) AS Result;  

Result 

------------- 

Developer 

 

(1 row(s) affected) 

Simple CHOOSE Example Based On Column

returns a simple character string based on the value in the ProductCategoryID column.

USE AdventureWorks2012; 

GO 

SELECT ProductCategoryID, CHOOSE (ProductCategoryID, 'A','B','C','D','E') AS Expression1 

FROM Production.ProductCategory; 

 

ProductCategoryID Expression1 

----------------- ----------- 

3                 C 

1                 A 

2                 B 

4                 D 

 

(4 row(s) affected) 

 

CHOOSE In Combination With MONTH

returns the season in which an employee was hired. The MONTH function is used to return the month value from the column HireDate.

USE AdventureWorks2012; 

GO 

SELECT JobTitle, HireDate,

CHOOSE(MONTH(HireDate),'Winter','Winter',                                             'Spring','Spring','Spring','Summer','Summer',

'Summer','Autumn','Autumn','Autumn','Winter') AS Quarter_Hired 

FROM HumanResources.Employee 

WHERE  YEAR(HireDate) > 2005 

ORDER BY YEAR(HireDate); 

JobTitle                                           HireDate   Quarter_Hired 

-------------------------------------------------- ---------- ------------- 

Sales Representative                               2006-11-01 Autumn 

European Sales Manager                             2006-05-18 Spring 

Sales Representative                               2006-07-01 Summer 

Sales Representative                               2006-07-01 Summer 

Sales Representative                               2007-07-01 Summer 

Pacific Sales Manager                              2007-04-15 Spring 

Sales Representative                               2007-07-01 Summer