SQL
Create a Simple Pivot Table (Cross-Tabulation) in SQL
Learn to transform rows into columns to create a summary pivot table in SQL, useful for reporting and data analysis.
-- Assuming a 'sales' table with 'region', 'product', 'revenue'
SELECT
product,
SUM(CASE WHEN region = 'North' THEN revenue ELSE 0 END) AS North_Revenue,
SUM(CASE WHEN region = 'South' THEN revenue ELSE 0 END) AS South_Revenue,
SUM(CASE WHEN region = 'East' THEN revenue ELSE 0 END) AS East_Revenue,
SUM(CASE WHEN region = 'West' THEN revenue ELSE 0 END) AS West_Revenue,
SUM(revenue) AS Total_Revenue
FROM sales
GROUP BY product
ORDER BY product;
-- Many databases (SQL Server, Oracle, PostgreSQL 14+) have PIVOT/CROSSTAB functions for more advanced dynamic pivoting.
How it works: This SQL snippet demonstrates a common method for creating a simple pivot table, also known as cross-tabulation. It transforms unique values from a 'region' column into separate columns, showing the aggregated 'revenue' for each 'product' and 'region' combination. This is achieved using `SUM()` with `CASE WHEN` statements to conditionally sum revenue based on the region. The `GROUP BY product` clause aggregates the results per product. This technique is highly useful for generating concise summary reports.