Using CASE
The CASE expression is used to evaluate several conditions and return a single value for each condition. For example, it 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.
The CASE expression 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.
An optional ELSE clause defining the expression returned if no comparison operation evaluates to TRUE.
The END keyword.
An optional AS clause defining an alias for the CASE expression.
A common use of the CASE expression is to replace codes or abbreviations with more readable values. The following example uses the CASE function to change the display of product line categories to make them more understandable.
USE AdventureWorks2008R2;
GO
SELECT ProductNumber, Category =
CASE ProductLine
WHEN 'R' THEN 'Road'
WHEN 'M' THEN 'Mountain'
WHEN 'T' THEN 'Touring'
WHEN 'S' THEN 'Other sale items'
ELSE 'Not for sale'
END,
Name
FROM Production.Product
ORDER BY ProductNumber;
GO
Another use of CASE is to categorize data. The following query uses the CASE function to categorize prices.
USE AdventureWorks2008R2;
GO
SELECT ProductNumber, Name, 'Price Range' =
CASE
WHEN ListPrice = 0 THEN 'Mfg item - not for resale'
WHEN ListPrice < 50 THEN 'Under $50'
WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250'
WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'
ELSE 'Over $1000'
END
FROM Production.Product
ORDER BY ProductNumber ;
GO
For more examples, see CASE (Transact-SQL).
Just to let know that you can use CASE with GROUP BY Clause , it's just not mentioned here so i thin it would be nice to mention it on MSDN and also we can use LIKE for comparision inside WHEN.
--Using CASE in a GROUP BY clause , also Using LIKE
USE AdventureWorks
GO
SELECT CASE
WHEN Title LIKE '%Account%' THEN 'Account'
WHEN Title LIKE '%Chief%' THEN 'Chief'
WHEN Title LIKE '%Database%' THEN 'Database'
WHEN Title LIKE '%Human Resources%' THEN 'Human Resources'
WHEN Title LIKE '%Netowrk%' THEN 'Network'
WHEN Title LIKE '%Production%' THEN 'Production'
WHEN Title LIKE '%Quality Assurance%' THEN 'Quality Assurance'
WHEN Title LIKE '%Research%' THEN 'Research'
WHEN Title LIKE '%Vice President%' THEN 'Vice President'
ELSE 'No Specific Title'
END AS Title
, COUNT(EmployeeID) AS NumberOfEmployee
FROM HumanResources.Employee
GROUP BY CASE
WHEN Title LIKE '%Account%' THEN 'Account'
WHEN Title LIKE '%Chief%' THEN 'Chief'
WHEN Title LIKE '%Database%' THEN 'Database'
WHEN Title LIKE '%Human Resources%' THEN 'Human Resources'
WHEN Title LIKE '%Netowrk%' THEN 'Network'
WHEN Title LIKE '%Production%' THEN 'Production'
WHEN Title LIKE '%Quality Assurance%' THEN 'Quality Assurance'
WHEN Title LIKE '%Research%' THEN 'Research'
WHEN Title LIKE '%Vice President%' THEN 'Vice President'
ELSE 'No Specific Title'
END
--Title NumberOfEmployee
------------------- ----------------
--Account 8
--Chief 3
--Database 2
--Human Resources 3
--No Specific Title 82
--Production 180
--Quality Assurance 6
--Research 4
--Vice President 2
--(9 row(s) affected)
- 10/14/2011
- Hasham Niaz
--Using IN Clause while comparing condition in CASE WEHN Clause
USE AdventureWorks
GO
SELECT CASE
WHEN Title IN('Mr.','Sr.','Sra.') THEN 'Mr.'
WHEN Title IN('Mrs.','Ms.','Ms') THEN 'Ms.'
ELSE 'N/A'
END AS Title
, FirstName , LastName
FROM Person.Contact
--Title FirstName LastName
------- ---------------- --------------------------------------------------
--Mr. Gustavo Achong
--Ms. Catherine Abel
--Ms. Kim Abercrombie
--Mr. Humberto Acevedo
--Mr. Pilar Ackerman
--(5 row(s) affected)
- 10/14/2011
- Hasham Niaz
