36. Advanced Patterns -- Pivot Tables & Recursive Queries

Level: Advanced SQL Server for real projects

ℹ️ What You'll Learn

• PIVOT syntax (rotate rows to columns)
• Unpivot (columns to rows)
• Recursive CTEs (hierarchical data)
• Multi-level aggregations
• Drill-down reporting
• Complex business logic queries
• Performance considerations
• Common patterns and mistakes

Pivot rotates data. Recursive queries handle hierarchies. This article teaches advanced reporting.

Why Patterns Matter for Backend Developers

Business requirements need sophisticated queries:

"Show exam grades per student, one row per student"
    v
Naive: Multiple SELECT per subject (slow)
Pivot: One row, one per subject column (fast)
    v
Dashboard: Instant grade matrix

Patterns solve complex requirements.

PIVOT Syntax

SELECT *
FROM SourceTable
PIVOT (
    AggregateFunction(Column)
    FOR PivotColumn IN ([Value1], [Value2], ...)
) AS PivotedTable;

Rotates rows (values) into columns.

Example 1: Exam Grades Pivot

Student grades by subject in one row:

SELECT *
FROM (
    SELECT 
        s.Name AS Student,
        sub.Name AS Subject,
        r.MarksObtained
    FROM Student s
    INNER JOIN ExamResult r ON s.StudentId = r.StudentId
    INNER JOIN Exam e ON r.ExamId = e.ExamId
    INNER JOIN Subject sub ON e.SubjectId = sub.SubjectId
) AS SourceData
PIVOT (
    MAX(MarksObtained)
    FOR Subject IN ([Maths], [Science], [English], [Hindi])
) AS PivotTable;

Result (one row per student):

Student     | Maths | Science | English | Hindi
Ravi Kumar  | 92    | 88      | 85      | 90
Priya Sharma| 85    | 92      | 88      | 87
Arjun Reddy | 88    | 85      | 92      | 85

Columns = subjects. One row per student.

Example 2: Fee Collection by Month

Monthly collection summary:

SELECT *
FROM (
    SELECT 
        s.Name AS Student,
        MONTH(p.PaidOn) AS Month,
        p.Amount
    FROM Student s
    INNER JOIN FeePayment p ON s.StudentId = p.StudentId
) AS SourceData
PIVOT (
    SUM(Amount)
    FOR Month IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
) AS PivotTable;

Result:

Student     | 1    | 2    | 3    | 4    | 5
Ravi Kumar  | 5000 | 5000 | NULL | 5000 | 5000
Priya Sharma| NULL | 5000 | 5000 | NULL | 5000

Payment per month, one row per student.

Example 3: Class Performance Matrix

Class statistics per subject:

SELECT *
FROM (
    SELECT 
        s.ClassName,
        sub.Name AS Subject,
        AVG(r.MarksObtained) AS AvgMarks
    FROM Student s
    INNER JOIN ExamResult r ON s.StudentId = r.StudentId
    INNER JOIN Exam e ON r.ExamId = e.ExamId
    INNER JOIN Subject sub ON e.SubjectId = sub.SubjectId
    GROUP BY s.ClassName, sub.Name
) AS SourceData
PIVOT (
    MAX(AvgMarks)
    FOR Subject IN ([Maths], [Science], [English])
) AS PivotTable;

Result:

ClassName | Maths | Science | English
10-A      | 85.5  | 82.3    | 87.2
10-B      | 78.5  | 80.2    | 83.5
11-A      | 88.2  | 85.5    | 89.3

Class average per subject (matrix).

Example 4: Recursive CTE (Drill-down Report)

Building hierarchy for drill-down:

WITH ClassHierarchy AS (
    -- Anchor: Root classes
    SELECT 
        ClassId,
        ClassName,
        NULL AS ParentId,
        0 AS Level,
        CAST(ClassName AS VARCHAR(MAX)) AS Path
    FROM Class
    WHERE ParentClassId IS NULL
    
    UNION ALL
    
    -- Recursive: Child classes
    SELECT 
        c.ClassId,
        c.ClassName,
        c.ParentClassId,
        ch.Level + 1,
        ch.Path + ' -> ' + c.ClassName
    FROM Class c
    INNER JOIN ClassHierarchy ch ON c.ParentClassId = ch.ClassId
    WHERE ch.Level < 5  -- Depth limit
)
SELECT * FROM ClassHierarchy
ORDER BY Path;

Result:

ClassName | Level | Path
10th      | 0     | 10th
10-A      | 1     | 10th -> 10-A
10-A-1    | 2     | 10th -> 10-A -> 10-A-1

Drill-down hierarchy with path.

Example 5: UNPIVOT (Columns to Rows)

Reverse of PIVOT:

-- Input: Wide format
CREATE TABLE StudentGrades (
    StudentId INT,
    Student NVARCHAR(100),
    Maths INT,
    Science INT,
    English INT
);

INSERT INTO StudentGrades VALUES
(1, 'Ravi Kumar', 92, 88, 85),
(2, 'Priya Sharma', 85, 92, 88);

-- UNPIVOT to long format
SELECT 
    StudentId,
    Student,
    Subject,
    Marks
FROM StudentGrades
UNPIVOT (
    Marks FOR Subject IN (Maths, Science, English)
) AS UnpivotTable;

Result:

StudentId | Student     | Subject | Marks
1         | Ravi Kumar  | Maths   | 92
1         | Ravi Kumar  | Science | 88
1         | Ravi Kumar  | English | 85
2         | Priya Sharma| Maths   | 85

Wide -> Long format (opposite of pivot).

Example 6: Multiple Aggregations (Drill-down)

Student performance with rollup:

WITH StudentPerformance AS (
    SELECT 
        s.StudentId,
        s.Name AS Student,
        s.ClassName,
        COUNT(r.ExamResultId) AS ExamsTaken,
        AVG(r.MarksObtained) AS AvgMarks,
        MAX(r.MarksObtained) AS BestMarks,
        MIN(r.MarksObtained) AS LowestMarks
    FROM Student s
    LEFT JOIN ExamResult r ON s.StudentId = r.StudentId
    GROUP BY s.StudentId, s.Name, s.ClassName
)
SELECT 
    ClassName,
    COUNT(*) AS StudentCount,
    AVG(AvgMarks) AS ClassAverage,
    MAX(BestMarks) AS HighestMark,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY AvgMarks) OVER () AS MedianMarks
FROM StudentPerformance
GROUP BY ClassName;

Result:

ClassName | StudentCount | ClassAverage | HighestMark | MedianMarks
10-A      | 3            | 85.5         | 92          | 87.5
10-B      | 2            | 79.5         | 88          | 84

Multi-level aggregation (student -> class -> school).

---

Common Pattern Mistakes

Mistake 1: PIVOT with unknown columns

Wrong:

-- Pivot on column with unknown values
PIVOT (... FOR Subject IN (...))
-- If new subject added: Query fails

Fix: Dynamic PIVOT:

DECLARE @Subjects NVARCHAR(MAX);
SELECT @Subjects = STRING_AGG(DISTINCT '[' + Name + ']', ',')
FROM Subject;

-- Use @Subjects in PIVOT IN clause
-- Handles new subjects automatically

Mistake 2: Recursive CTE infinite loop

Wrong:

WITH Hierarchy AS (
    SELECT ClassId, ParentClassId FROM Class
    UNION ALL
    SELECT c.ClassId, c.ParentClassId
    FROM Class c
    INNER JOIN Hierarchy h ON c.ClassId = h.ParentClassId
    -- If circular relationship: infinite loop
)
SELECT * FROM Hierarchy;

Fix: Add depth limit:

WHERE h.Level < 10  -- Stop at 10 levels

Mistake 3: Pivot with NULL aggregate

Wrong:

PIVOT (
    AVG(MarksObtained)  -- Null-handling issue
    FOR Subject IN (...)
)
-- NULL subjects collapse into NULL column

Fix: Filter NULL:

PIVOT (
    AVG(ISNULL(MarksObtained, 0))
    FOR Subject IN (...)
)

Mistake 4: Performance -- huge pivot result

Wrong:

PIVOT over 1000 subjects
-- Result: 1000 columns (too wide, slow)

Fix: Limit pivoted columns:

-- Pivot top 10 subjects only
WHERE Subject IN (SELECT TOP 10 Name FROM Subject ORDER BY StudentCount DESC)

---

Best Practices for Advanced Patterns

1. PIVOT for reporting -- Not frequent queries
2. Limit pivot columns -- Too many slow
3. Recursive depth limit -- Prevent infinite loops
4. Test performance -- Pivots on large data slow
5. Document complex queries -- Explain logic
6. Use derived tables -- Separate pivot source
7. Index pivot columns -- Speed up source table
8. Consider alternative -- CASE may be simpler

---

🎯 Q1: What does PIVOT do?

Rotates rows to columns:

Long format:
Student | Subject | Marks
Ravi    | Maths   | 92
Ravi    | Science | 88

Pivoted:
Student | Maths | Science
Ravi    | 92    | 88

PIVOT: Rotate values into columns.

🎯 Q2: When should I use PIVOT?

Good: Reporting (one row per entity, attributes as columns). Bad: Frequent operational queries.

PIVOT for: Dashboards, exports. Not real-time APIs.

🎯 Q3: What's UNPIVOT?

Opposite of PIVOT (columns to rows):

Wide format: Student, Maths, Science, English
Long format: Student, Subject, Marks

UNPIVOT reverses PIVOT.

🎯 Q4: How do I handle unknown PIVOT columns?

Use STRING_AGG:

DECLARE @Cols NVARCHAR(MAX);
SELECT @Cols = STRING_AGG('[' + Subject + ']', ',')
FROM Subject;

-- Use @Cols in PIVOT IN clause
-- Handles new subjects dynamically

Dynamic PIVOT adapts to data changes.

🎯 Q5: How do I prevent recursive CTE infinite loop?

Add depth limit:

WITH Recursive AS (
    ...
    WHERE Level < 10  -- Stop at 10 levels
)
SELECT * FROM Recursive;

Prevents circular references.

🎯 Q6: Is PIVOT faster than CASE?

PIVOT: Cleaner syntax, slower on large datasets. CASE: More verbose, can be faster.

-- PIVOT syntax cleaner
-- CASE may be faster for complex logic
-- Choose based on performance testing

Test both for your use case.

---

🤖Use AI to Learn Faster

⚠️ Important for beginners: Do NOT use AI to write your code yet. Type every example yourself. Your brain learns by doing, not by reading AI output. Use AI only to explain and quiz you — not to code for you. Once you have strong fundamentals, AI becomes a powerful productivity tool for repetitive tasks.

Use ChatGPT, Claude, or Copilot to go deeper on Advanced patterns and pivot tables in SQL Server. Try these prompts:

• "Show PIVOT: student grades per subject in one row"
• "How do I unpivot (columns to rows)?"
• "Explain recursive CTE: class hierarchy drill-down"
• "When should I use PIVOT vs CASE statements?"
• "Quiz me: ask 5 questions about PIVOT, recursive CTEs, aggregation patterns"

💡 Tip: After reading this article, paste your own code into AI and ask "What could go wrong here and why?" — fastest way to find edge cases and deepen understanding.

Next Article

37. Real-World SMS Scenarios -- Complete Examples ->