Converting rows into columns is often referred to as "pivoting." In SQL, you can achieve this using the CASE statement in conjunction with an aggregate function, typically SUM() or COUNT().
Here's a generic way to convert rows into columns using the CASE statement:
Example Scenario
Suppose you have a table called Sales with the following structure:
| ID | Product | Amount |
|----|-----------|--------|
| 1 | A | 100 |
| 2 | B | 200 |
| 3 | A | 150 |
| 4 | B | 250 |
| 5 | C | 300 |
Goal
You want to transform this table so that each product becomes a column, and the rows are aggregated by the Amount.
SQL Query
Here’s how you’d write a SQL query to achieve that with the CASE statement:
SELECT
SUM(CASE WHEN Product = 'A' THEN Amount ELSE 0 END) AS Product_A,
SUM(CASE WHEN Product = 'B' THEN Amount ELSE 0 END) AS Product_B,
SUM(CASE WHEN Product = 'C' THEN Amount ELSE 0 END) AS Product_C
FROM
Sales;
Explanation
1. CASE Statement: For each product, we use a CASE statement to assign the Amount to its respective product column. If the condition (Product = 'A', Product = 'B', etc.) is true, it returns the Amount. Otherwise, it returns 0.
2. SUM Function: The SUM function is used to aggregate the results, ensuring that all amounts for each product are summed together.
3. Column Aliasing: We alias each sum with a meaningful column name (Product_A, Product_B, etc.) for clarity in the result set.
Result
The output of this query will be:
| Product_A | Product_B | Product_C |
|-----------|-----------|-----------|
| 250 | 450 | 300 |
In this result:
- 250 is the total for Product A (100 + 150).
- 450 is the total for Product B (200 + 250).
- 300 is the total for Product C.
Additional Notes
- If you have more products, you'll need to extend the CASE statements accordingly.
- It's important to consider performance for large datasets, as using multiple CASE statements can become cumbersome. In such cases, consider using more advanced techniques like Common Table Expressions (CTEs) or specific pivot functions if supported by your SQL dialect.