Using CASE
The CASE function is a special Transact-SQL expression that allows an alternative value to be displayed depending on the value of a column. This change in data is temporary; therefore, there are no permanent changes to the data. For example, the CASE function can display California in a query result set for rows that have the value CA in the state column.
The CASE function consists of:
- The CASE keyword.
- The column name to transform.
- WHEN clauses specifying the expressions to search for and THEN clauses specifying the expressions to replace them with.
- The END keyword.
- An optional AS clause defining an alias for the CASE function.
This example displays, in the query result set, the full name of the state each author lives in:
SELECT au_fname, au_lname,
CASE state
WHEN 'CA' THEN 'California'
WHEN 'KS' THEN 'Kansas'
WHEN 'TN' THEN 'Tennessee'
WHEN 'OR' THEN 'Oregon'
WHEN 'MI' THEN 'Michigan'
WHEN 'IN' THEN 'Indiana'
WHEN 'MD' THEN 'Maryland'
WHEN 'UT' THEN 'Utah'
END AS StateName
FROM pubs.dbo.authors
ORDER BY au_lname