Master MySQL Row-to-Column Pivot: From Basics to Advanced Aggregations

Recently I have been learning data warehouse architecture and building business reports, encountering needs such as merging multiple rows into a single line, creating dynamic column reports, and converting rows to columns.

Storage Types

Before pivoting rows to columns, we need to understand the underlying storage concepts.

OLAP (Online Analytical Processing) is mainly used for data analysis and reporting, with low data freshness and suitable for columnar storage.

OLTP (Online Transaction Processing) stores transactional data with ACID guarantees, high timeliness, and is typically row‑oriented, as seen in typical order tables.

In columnar storage, multiple dimensions of an event are stored as separate columns, facilitating statistical analysis by grouping and aggregating rows into columns.

Aggregation Functions

Sum

Calculate each user's completed, cancelled, and pending amounts.

SELECT
    user_id,
    SUM(CASE WHEN status = '0' THEN price ELSE 0 END) AS completed_amount,
    SUM(CASE WHEN status = '1' THEN price ELSE 0 END) AS cancelled_amount,
    SUM(CASE WHEN status = '2' THEN price ELSE 0 END) AS pending_amount
FROM orders
GROUP BY user_id;

Count

Count the number of completed, cancelled, and pending orders per user.

SELECT
    user_id,
    COUNT(CASE WHEN status = '0' THEN 1 END) AS completed_orders,
    COUNT(CASE WHEN status = '1' THEN 1 END) AS cancelled_orders,
    COUNT(CASE WHEN status = '2' THEN 1 END) AS pending_orders
FROM orders
GROUP BY user_id;

Average

Compute the average completed, cancelled, and pending amounts per user.

SELECT
    user_id,
    AVG(CASE WHEN status = '0' THEN price ELSE 0 END) AS avg_completed,
    AVG(CASE WHEN status = '1' THEN price ELSE 0 END) AS avg_cancelled,
    AVG(CASE WHEN status = '2' THEN price ELSE 0 END) AS avg_pending
FROM orders
GROUP BY user_id;

Maximum

Find each user's order with the highest profit (price minus cost).

SELECT
    user_id,
    MAX(price - cost) AS max_profit
FROM orders
GROUP BY user_id;

These aggregation queries are fast for tables with daily increments under 500 k rows and total size below 50 million rows, provided the database server has adequate hardware (e.g., 32 CPU cores, 64 GB RAM). For billion‑row scales or daily increments of millions, an offline storage system or a more robust architecture should be considered.

Optimizing database performance and scaling hardware remain the primary drivers of productivity.