Transposing Rows to Columns

In this lesson, you will learn how to transform "long" datasets—where attributes are stored in separate rows—into "wide" reports that are easier for stakeholders to read.

Why Transpose?

Often, databases store data in a normalized, narrow format. For example, a sales table might look like this:

Category	Month	Revenue
Electronics	Jan	100
Electronics	Feb	150
Furniture	Jan	80

While this is great for storage, it is difficult to analyze month-over-month. To create a report, we want the months as columns, not rows. This process is called transposing.

The Technique: Conditional Aggregation

The most reliable way to transpose data without specialized pivot commands is to use conditional aggregation. By combining CASE statements with an aggregate function like SUM, we can "filter" row values into their own columns based on a condition.

To convert our table, we group by the Category and use a CASE statement to isolate values for each month:

SELECT 
    Category,
    SUM(CASE WHEN Month = 'Jan' THEN Revenue ELSE 0 END) AS Jan_Revenue,
    SUM(CASE WHEN Month = 'Feb' THEN Revenue ELSE 0 END) AS Feb_Revenue
FROM SalesTable
GROUP BY Category;

How it Works

1. GROUP BY Category: This collapses all individual row entries into one single row per category.
2. CASE WHEN: We evaluate every row for a specific month. If the condition is met, we keep the Revenue; if not, we return 0 (or NULL).
3. SUM(): Since our GROUP BY has collapsed the rows, the SUM function adds up the "filtered" revenue for that specific month column.

By shifting specific attributes into the column headers, you turn a long, sparse dataset into a clean, readable summary table ready for a spreadsheet or executive dashboard.

Key Takeaway: You can transpose rows into columns by grouping your data and using conditional aggregation (CASE statements inside SUM/MAX) to distribute row values into specific, labeled columns.