Pivoting Data in SQL - Transforming Rows to Columns

Introduction

Pivoting data means transforming rows into columns. It’s like taking a list and turning it into a spreadsheet-style table where categories become column headers.

Why Pivoting Matters?

It’s useful when you need to:

• Create summary reports
• Compare categories side by side
• Transform data for visualization
• Make data more readable

Main Content: Understanding Pivoting

Let’s use this simple table:

-- Create a simple table
CREATE TABLE student_scores (
    student_name VARCHAR(50),
    subject VARCHAR(50),
    score INT
);

-- Insert sample data
INSERT INTO student_scores VALUES
('Alice', 'Math', 85),
('Alice', 'Science', 90),
('Alice', 'History', 88),
('Bob', 'Math', 78),
('Bob', 'Science', 85),
('Bob', 'History', 82),
('Charlie', 'Math', 90),
('Charlie', 'Science', 92),
('Charlie', 'History', 85);

Example 1: Basic Pivot Using CASE

Let’s transform subjects into columns:

SELECT 
    student_name,
    MAX(CASE WHEN subject = 'Math' THEN score END) as math_score,
    MAX(CASE WHEN subject = 'Science' THEN score END) as science_score,
    MAX(CASE WHEN subject = 'History' THEN score END) as history_score
FROM student_scores
GROUP BY student_name;

This query:

1. Takes each student
2. Creates a column for each subject
3. Places scores in appropriate columns

Results:

student_name | math_score | science_score | history_score
-------------|------------|---------------|---------------
Alice        | 85         | 90            | 88
Bob          | 78         | 85            | 82
Charlie      | 90         | 92            | 85

Example 2: Adding Totals to Pivoted Data

Let’s include total and average scores:

SELECT 
    student_name,
    MAX(CASE WHEN subject = 'Math' THEN score END) as math_score,
    MAX(CASE WHEN subject = 'Science' THEN score END) as science_score,
    MAX(CASE WHEN subject = 'History' THEN score END) as history_score,
    SUM(score) as total_score,
    ROUND(AVG(score), 2) as average_score
FROM student_scores
GROUP BY student_name;

This query shows:

1. Individual subject scores
2. Total of all scores
3. Average score per student

Results:

student_name | math_score | science_score | history_score | total_score | average_score
-------------|------------|---------------|---------------|-------------|---------------
Alice        | 85         | 90            | 88            | 263         | 87.67
Bob          | 78         | 85            | 82            | 245         | 81.67
Charlie      | 90         | 92            | 85            | 267         | 89.00

Example 3: Simple Cross-Tab Report

Let’s create a summary showing count of scores by range:

SELECT 
    CASE 
        WHEN score >= 90 THEN '90-100'
        WHEN score >= 80 THEN '80-89'
        ELSE 'Below 80'
    END as score_range,
    COUNT(CASE WHEN subject = 'Math' THEN 1 END) as math_count,
    COUNT(CASE WHEN subject = 'Science' THEN 1 END) as science_count,
    COUNT(CASE WHEN subject = 'History' THEN 1 END) as history_count
FROM student_scores
GROUP BY 
    CASE 
        WHEN score >= 90 THEN '90-100'
        WHEN score >= 80 THEN '80-89'
        ELSE 'Below 80'
    END;

This query:

1. Groups scores into ranges
2. Counts scores in each range by subject
3. Shows distribution of scores

Results:

score_range | math_count | science_count | history_count
------------|------------|---------------|---------------
90-100      | 1          | 2             | 0
80-89       | 1          | 1             | 3
Below 80    | 1          | 0             | 0

Best Practices

1. Planning the Pivot

   • Identify rows to become columns
   • Choose meaningful column names
   • Consider number of resulting columns

2. Data Preparation

   • Clean data before pivoting
   • Handle NULL values
   • Check for unexpected values

3. Column Management

   • Keep number of columns reasonable
   • Use clear naming conventions
   • Document transformation logic

4. Common Uses

   • Monthly reports
   • Score cards
   • Performance summaries
   • Category comparisons

5. Things to Remember

   • Use GROUP BY with pivots
   • Handle missing values
   • Consider data volume
   • Keep it readable

Remember:

• Pivoting transforms rows to columns
• CASE statements help create columns
• GROUP BY is usually needed
• Keep it simple and clear

Common Mistakes to Avoid:

1. Too many pivot columns
2. Missing GROUP BY
3. Incorrect aggregation
4. Not handling NULLs

Happy querying! 🚀