How Can I Condense Data in MySQL Using a CASE Statement?

Condensing Data with a Case Statement in MySQL

When working with data stored in relational databases, it can be useful to manipulate the contents of columns based on specific criteria. This is where the CASE statement comes into play, providing a way to dynamically assign values to columns based on conditional expressions, allowing you to restructure your data for enhanced analysis and reporting.

Example: Generating Conditional Columns

Suppose you have a database table called 'tbl_transaction' with columns for id, action_type, action_heading, and action_amount. To generate two new columns, Income Amt and Expense Amt, we can leverage the CASE statement to populate them conditionally based on the action_type column:

SELECT
    id,
    action_heading,
    CASE
        WHEN action_type = 'Income' THEN action_amount
        ELSE NULL
    END AS income_amt,
    CASE
        WHEN action_type = 'Expense' THEN action_amount
        ELSE NULL
    END AS expense_amt

FROM tbl_transaction;

This query will produce output similar to:

ID	Heading	Income Amt	Expense Amt
1	ABC	1000	NULL
2	XYZ	NULL	2000

As you can see, the Income Amt column is populated with values for Income transactions, while the Expense Amt column is populated with values for Expense transactions.

Alternative Syntax

MySQL also provides the IF() function as an alternative to the CASE statement for handling conditional operations. However, the CASE statement is preferable in this case as it is more versatile and portable across various database engines.

The above is the detailed content of How Can I Condense Data in MySQL Using a CASE Statement?. For more information, please follow other related articles on the PHP Chinese website!