How to Use the CASE Statement in SQL for Conditional Logic

The CASE statement in SQL allows you to implement conditional logic within queries, making it a powerful tool for handling complex data transformations and classifications.

Basic Syntax

The CASE statement works like an IF-ELSE structure, evaluating conditions and returning corresponding values:

SELECT column_name,
       CASE
           WHEN condition1 THEN result1
           WHEN condition2 THEN result2
           ELSE default_result
       END AS alias_name
FROM table_name;

Example: Categorizing Data

Assume we have an Employees table with an Age column, and we want to categorize employees based on their age groups:

SELECT Name, Age,
       CASE
           WHEN Age < 25 THEN 'Young'
           WHEN Age BETWEEN 25 AND 40 THEN 'Mid-Age'
           ELSE 'Senior'
       END AS AgeCategory
FROM Employees;

Output Example:

Name Age AgeCategory
Alice 22 Young
Bob 30 Mid-Age
Charlie 50 Senior

Using CASE in Aggregations

CASE is often used in aggregate functions to perform conditional counting or summing:

SELECT
    SUM(CASE WHEN Age < 25 THEN 1 ELSE 0 END) AS YoungCount,
    SUM(CASE WHEN Age BETWEEN 25 AND 40 THEN 1 ELSE 0 END) AS MidAgeCount,
    SUM(CASE WHEN Age > 40 THEN 1 ELSE 0 END) AS SeniorCount
FROM Employees;

Using CASE in ORDER BY

You can use CASE to customize sorting order dynamically:

SELECT Name, Age
FROM Employees
ORDER BY
    CASE
        WHEN Age < 25 THEN 1
        WHEN Age BETWEEN 25 AND 40 THEN 2
        ELSE 3
    END;

Conclusion

The CASE statement is a versatile tool in SQL for implementing conditional logic in SELECT, WHERE, ORDER BY, and aggregate functions. It enhances query flexibility, making data classification and transformation more efficient.

0
33

Related

XML (Extensible Markup Language) is a widely used format for storing and transporting data.

In C#, you can create XML files efficiently using the XmlWriter and XDocument classes. This guide covers both methods with practical examples.

Writing XML Using XmlWriter

XmlWriter provides a fast and memory-efficient way to generate XML files by writing elements sequentially.

Example:

using System;
using System.Xml;

class Program
{
    static void Main()
    {
        using (XmlWriter writer = XmlWriter.Create("person.xml"))
        {
            writer.WriteStartDocument();
            writer.WriteStartElement("Person");

            writer.WriteElementString("FirstName", "John");
            writer.WriteElementString("LastName", "Doe");
            writer.WriteElementString("Age", "30");

            writer.WriteEndElement();
            writer.WriteEndDocument();
        }
        Console.WriteLine("XML file created successfully.");
    }
}

Output (person.xml):

<?xml version="1.0" encoding="utf-8"?>
<Person>
    <FirstName>John</FirstName>
    <LastName>Doe</LastName>
    <Age>30</Age>
</Person>

Writing XML Using XDocument

The XDocument class from LINQ to XML provides a more readable and flexible way to create XML files.

Example:

using System;
using System.Xml.Linq;

class Program
{
    static void Main()
    {
        XDocument doc = new XDocument(
            new XElement("Person",
                new XElement("FirstName", "John"),
                new XElement("LastName", "Doe"),
                new XElement("Age", "30")
            )
        );
        doc.Save("person.xml");
        Console.WriteLine("XML file created successfully.");
    }
}

This approach is ideal for working with complex XML structures and integrating LINQ queries.

When to Use Each Method

  • Use XmlWriter when performance is critical and you need to write XML sequentially.
  • Use XDocument when you need a more readable, maintainable, and flexible way to manipulate XML.

Conclusion

Writing XML files in C# is straightforward with XmlWriter and XDocument. Choose the method that best suits your needs for performance, readability, and maintainability.

1
240

When working with URLs in C#, encoding is essential to ensure that special characters (like spaces, ?, &, and =) don’t break the URL structure. The recommended way to encode a string for a URL is by using Uri.EscapeDataString(), which converts unsafe characters into their percent-encoded equivalents.

string rawText = "hello world!";
string encodedText = Uri.EscapeDataString(rawText);

Console.WriteLine(encodedText); // Output: hello%20world%21

This method encodes spaces as %20, making it ideal for query parameters.

For ASP.NET applications, you can also use HttpUtility.UrlEncode() (from System.Web), which encodes spaces as +:

using System.Web;

string encodedText = HttpUtility.UrlEncode("hello world!");
Console.WriteLine(encodedText); // Output: hello+world%21

For .NET Core and later, Uri.EscapeDataString() is the preferred choice.

27
1082

In C#, you can format an integer with commas (thousands separator) using ToString with a format specifier.

int number = 1234567;
string formattedNumber = number.ToString("N0"); // "1,234,567"
Console.WriteLine(formattedNumber);

Explanation:

"N0": The "N" format specifier stands for Number, and "0" means no decimal places. The output depends on the culture settings, so in regions where , is the decimal separator, you might get 1.234.567.

Alternative:

You can also specify culture explicitly if you need a specific format:

using System.Globalization;

int number = 1234567;
string formattedNumber = number.ToString("N0", CultureInfo.InvariantCulture);
Console.WriteLine(formattedNumber); // "1,234,567"
3
391