How to Write a CASE Statement in SQL Server

SQL Server's `CASE` statement allows you to implement conditional logic within your queries. This means you can return different results depending on the conditions specified. Let's explore how to use this feature effectively.

What is a CASE Statement?

The `CASE` statement is SQL's way of handling conditional logic. It enables you to evaluate each row based on specific criteria and return a value when a condition is met.

Basic Syntax

SELECT column1,
       column2,
       CASE 
           WHEN condition1 THEN result1
           WHEN condition2 THEN result2
           ELSE result3
       END as conditional_result
FROM your_table;

Example Usage

Let’s say you want to assign a value based on a specific condition. Here's an example where we categorize employees based on their salary.

SELECT name,
       salary,
       CASE
           WHEN salary >= 50000 THEN 'High'
           WHEN salary >= 30000 THEN 'Medium'
           ELSE 'Low'
       END as salary_category
FROM employees;

Using CASE with Aggregation

The `CASE` statement can also be used with aggregation functions like `COUNT()`, `SUM()`, etc., to apply conditional logic on aggregated data.

SELECT department,
       COUNT(CASE WHEN salary >= 50000 THEN 1 END) AS high_salary_count
FROM employees
GROUP BY department;

Nested CASE Statements

In some cases, you might need to nest `CASE` statements. You can nest a `CASE` inside another `CASE` to handle more complex conditions.

SELECT name,
       salary,
       CASE
           WHEN salary >= 50000 THEN 
               CASE 
                   WHEN department = 'IT' THEN 'High IT Salary'
                   ELSE 'High Non-IT Salary'
               END
           ELSE 'Low Salary'
       END as salary_category
FROM employees;

Conclusion

The `CASE` statement in SQL Server provides a powerful way to implement logic directly in your queries. It is versatile and can be used for a variety of purposes, from simple condition checks to more complex logic within aggregation functions.