Case
The Power of SQL CASE Statements
found here http://www.4guysfromrolla.com/webtech/102704-1.shtml
CASE Basics
All programming languages contain conditional statements to allow program flow to be altered based on the result of some conditional statement, the simplest form of a conditional statement typically being an If statement. One downside of If statements is that if there are a number of different actions that must be taken based on some variable value, the number of If statements can explode, leading to bloated, unreadable code. For example, imagine that you wanted to do some different action based on the hour of the day. If a different action was performed for each hour of the day, you would end up having 24 If/ElseIf statements like so:
'Do whatever needs to be done at midnight'
ElseIf Hour(Now()) = 1 Then
'Do whatever needs to be done at 1:00 am'
ElseIf Hour(Now()) = 2 Then
...
...
End If
The multiple If statements detract from the readability of the code. Also, the maintainability of the code suffers. Imagine that you needed to change the code so that task x was run at both 3:00 am and 9:00 am. This would require picking your way through the If statements and adding additional conditional statements where needed.
Many programming languages provide case statements: Visual Basic offers a Select Case, C#/C/C++/Java the switch statement. These case statements allow for the myriad of If statements to be compressed into a more readable block of code. An example Select Case statement can be seen below:
CASE 0
'Do whatever needs to be done at midnight'
CASE 1
'Do whatever needs to be done at 1:00 am'
CASE 2
...
...
END SELECT
Not to be outdone, T-SQL contains a CASE statement as well. The T-SQL CASE statement has two flavors:
1. A simple case statement, whose syntax and semantics closely mirror those case statements found in other programming languages. The generic form for the simple case statement is:
WHEN value1 THEN result1
WHEN value2 THEN result2
...
WHEN valueN THEN resultN
[
ELSE elseResult
]
END
2. A "searched CASE form" that instead of specifying a single expression, contains a series of Boolean expressions in each of the WHEN clauses:
WHEN booleanExpression1 THEN result1
WHEN booleanExpression2 THEN result2
...
WHEN booleanExpressionN THEN resultN
[
ELSE elseResult
]
END
Now that we've looked at the generic syntax for T-SQL CASE statements, let's turn our attention to examining how CASE can be used in real-world applications.
Prettying Up Your Output
The simplest use of CASE is for tweaking the output. For example, imagine you have a table with employee information that contains a char(1) field called Gender, with a value of M for males and F for females. You might want to have the output be Male and Female instead of M and F. This can easily be accomplished with CASE as the following example illustrates:
FirstName, LastName,
Salary, DOB,
CASE Gender
WHEN 'M' THEN 'Male'
WHEN 'F' THEN 'Female'
END
FROM Employees
For more examples of enhancing the output with CASE be sure to read The Case for CASE.
Formatting Summation Columns
The SQL syntax offers a number of aggregate functions, such as AVG(), SUM(), MAX(), and so forth, allowing for averages, sums, maxes, minimums, and so on to be grouped by particular field values. Returning to the employees example, if each employee worked for a department specified by a Department field, and if each employee had a Salary field, you could display the average salary and total salary per department using a query like:
Department, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department
The output of this query might look like:
Department AvgSalary ----------------------------------- Sales 78,500.00 Marketing 81,250.00 IT 55,000.50 Executive 91,900.75
You might also want to know the average salary for the entire company, across all departments. SQL provides the ROLLUP keyword to summarize aggregated data. For example, adjusting the query by adding WITH ROLLUP, as in:
Department, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department
WITH ROLLUP
Might generate the following output:
Department AvgSalary ----------------------------------- Sales 78,500.00 Marketing 81,250.00 IT 55,000.50 Executive 91,900.75 NULL 76,662.81
The problem here is that the last row that provides the summary has a NULL value for the Department field. Ideally, this would have a value like Company Average. Well, SQL provides a GROUPING(columnName) function that returns a 1 when the specified column name is for a summarized row. Using this inside a CASE statement, you could achieve the desired output like so:
CASE
WHEN GROUPING(Department) = 1 THEN 'Company Average'
ELSE Department
END AS Department,
AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department
WITH ROLLUP
For more on this technique, including how to display summarized data within a DataGrid, be sure to read Dave Long's article Summarizing Data with ROLLUP.