How to Pivot Rows to Columns in SQL?
Transform your SQL data by pivoting rows into columns using SUM and CASE WHEN, simplifying your data analysis.
Formula Genius can generate a draft from a plain-English prompt. Supported formulas can be checked with sample cases; review the coverage on the result.
The formula
"Pivot rows to columns by using SUM(CASE WHEN category = 'X' THEN value END) for each category"
SELECT category, SUM(CASE WHEN category = 'X' THEN value END) AS X_value, SUM(CASE WHEN category = 'Y' THEN value END) AS Y_value FROM your_table GROUP BY category;This SQL formula pivots rows into columns based on specified categories and sums their values.
Step-by-step breakdown
- Identify the categories you want to pivot.
- Use the SUM function to aggregate values for each category.
- Implement CASE WHEN to filter values based on category.
- Group the results by the original category to create distinct rows.
Edge cases & warnings
- No data for a specific category results in NULL values.
- Multiple rows with the same category will be aggregated correctly.
- Categories with zero values will still appear in the result set if included in the CASE statement.
Examples
"SELECT category, value FROM sales_data;"
Category A: 100, Category B: 200"SELECT category, SUM(value) FROM sales_data GROUP BY category;"
Category A: 150, Category B: 300Frequently asked questions
What if my categories have NULL values?
NULL values will not be included in the SUM calculation.
Can I pivot more than two categories?
Yes, you can add more SUM(CASE WHEN ...) statements for additional categories.
How do I handle dynamic categories?
For dynamic categories, consider using a dynamic SQL approach or a pivot table feature if available.
Can't find what you need?
Describe a formula in plain English. Formula Genius drafts and explains it, and can check supported formulas on sample cases.