How to Pivot Rows to Columns in SQL?
Pivot Rows to Columns is a SQL query that this sql formula pivots rows into columns based on specified categories and sums their values.. Formula Genius generates and validates this formula automatically from a plain-English prompt.
Transform your SQL data by pivoting rows into columns using SUM and CASE WHEN, simplifying your data analysis.
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 any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.