Pivot rows into columns — build a cross-tab report (MySQL has no native PIVOT)

Question

Accepted Answer

96
   Pivot rows into columns — build a cross-tab report without native PIVOT
  Level: Hard — A very common reporting request; MySQL lacks PIVOT, so you must know the conditional-aggregation trick.

  
     Scenario: Sales data is stored one row per (product, month). Management wants a spreadsheet-style report: one row per product, with a column for each month (Jan, Feb, Mar). MySQL has no PIVOT keyword like SQL Server. How do you transform rows into columns?
  

   Sample Data — sales (row-based / "long" format)
  
    
      
        productmonthamount
      
      
        LaptopJan1000
        LaptopFeb1500
        LaptopMar1200
        PhoneJan800
        PhoneFeb950
      
    
  

   Desired Output (pivoted / "wide" format)
  
    
      
        productJanFebMar
      
      
        Laptop100015001200
        Phone8009500
      
    
  

  
     The technique — conditional aggregation: Combine CASE (or IF) with an aggregate like SUM(). Each future column becomes SUM(CASE WHEN month = 'Jan' THEN amount ELSE 0 END). The GROUP BY product collapses everything into one row per product.
  

   Solution — Conditional Aggregation (the standard pivot)
SELECT
    product,
    SUM(CASE WHEN month = 'Jan' THEN amount ELSE 0 END) AS Jan,
    SUM(CASE WHEN month = 'Feb' THEN amount ELSE 0 END) AS Feb,
    SUM(CASE WHEN month = 'Mar' THEN amount ELSE 0 END) AS Mar
FROM sales
GROUP BY product
ORDER BY product;

   Shorter Variant Using IF()
-- IF() is MySQL-specific shorthand for CASE WHEN
SELECT
    product,
    SUM(IF(month = 'Jan', amount, 0)) AS Jan,
    SUM(IF(month = 'Feb', amount, 0)) AS Feb,
    SUM(IF(month = 'Mar', amount, 0)) AS Mar
FROM sales
GROUP BY product;

  
     Why SUM and not MAX? If a product could have multiple rows for the same month, SUM correctly totals them. MAX(CASE ...) also works when there's exactly one value per cell, but SUM with ELSE 0 is the safer default for numeric pivots.
  

   The Problem: Hard-Coded Columns
  The big limitation is that you must know the column values (Jan, Feb, Mar) in advance. If months are dynamic (e.g., 12 months, or unknown categories), you need a dynamic pivot using prepared statements that build the SQL string.

   Dynamic Pivot (auto-generate the CASE columns)
-- Build the column list dynamically from the distinct months
SET @sql = NULL;
SELECT GROUP_CONCAT(
    DISTINCT CONCAT(
        'SUM(CASE WHEN month = ''', month, ''' THEN amount ELSE 0 END) AS `', month, '`'
    )
) INTO @sql
FROM sales;

-- Assemble and run the full pivot query
SET @sql = CONCAT('SELECT product, ', @sql,
                  ' FROM sales GROUP BY product ORDER BY product');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

  
     Interviewer follow-up: "When would you pivot in SQL vs in the application layer?" → For a fixed, small set of columns, SQL conditional aggregation is clean and efficient. For many or unknown columns, it's often better to return the raw "long" rows and pivot in the application/reporting layer (Excel, BI tools, pandas) — keeping the SQL simple and flexible.

Pivot rows into columns — build a cross-tab report (MySQL has no native PIVOT)

Answer with Explanation

Pivot rows into columns — build a cross-tab report without native PIVOT

Sample Data — `sales` (row-based / "long" format)

Desired Output (pivoted / "wide" format)

Solution — Conditional Aggregation (the standard pivot)

Shorter Variant Using IF()

The Problem: Hard-Coded Columns

Dynamic Pivot (auto-generate the CASE columns)

product	month	amount
Laptop	Jan	1000
Laptop	Feb	1500
Laptop	Mar	1200
Phone	Jan	800
Phone	Feb	950