Pivoting Rows to Columns

Part of the Beyond the Basics section of Coddy's SQL journey. Lesson 14 of 27.

Combine conditional aggregation with GROUP BY to turn rows into columns. It's the SQL version of a spreadsheet pivot.

Suppose sales has rows like (month, product, units). To see one row per month with one column per product:

SELECT
    month,
    SUM(CASE WHEN product = 'A' THEN units ELSE 0 END) AS product_a,
    SUM(CASE WHEN product = 'B' THEN units ELSE 0 END) AS product_b
FROM sales
GROUP BY month

The GROUP BY bucket each SUM(CASE …) sees is the rows for one month; the CASE picks the slice for one product.

Challenge

Easy

Available tables and columns:
sales: month, product, units

Pivot the data so each row is one month with these columns:

month
apples: total units for product 'apples' that month
bananas: same for 'bananas'
cherries: same for 'cherries'
total: total units across all products that month

Return only months whose total is at least 20. Order by month.

Try it yourself

SELECT month,
       -- one SUM(CASE ...) per product, plus total
FROM sales
GROUP BY month
-- filter to months with total >= 20
ORDER BY month

Test yourself

This lesson includes a short quiz. Start the lesson to answer it and track your progress.

All lessons in Beyond the Basics

1Conditional Logic

CASE WHEN CASE in ORDER BY COALESCE for NULLs NULLIF and IIF Recap - Attendance

4Set Operations

UNION vs UNION ALL INTERSECT EXCEPT Recap - Membership

2String Functions

LENGTH, UPPER, LOWER SUBSTR INSTR REPLACE and TRIM Concatenating with ||Recap - Invoices

5Recursive CTEs

A Number Sequence Walking a Hierarchy Recap - Org Chart

3Conditional Aggregation

COUNT with CASE SUM with CASEPivoting Rows to ColumnsRecap - Traffic Sources

6Analytics Recipes

Top-N per Group Percentage of Total Running Total Keeping One Row per Key Recap - Top Customers

Practice on your own: SQL playground