How to Dynamically Pivot Columns in SQL for Learner Report Module Completions

Summary: Learn how to use SQL to dynamically pivot columns for varying module completions in a learner report. This guide is designed for intermediate to advanced SQL users.
---

How to Dynamically Pivot Columns in SQL for Learner Report Module Completions

Working with educational data often involves dealing with learner reports detailing module completions. A common challenge is dynamically pivoting columns to represent varying module completions. This guide will walk you through the process of achieving this in SQL, specifically using SQL Server.

Understanding Dynamic Pivoting

Pivoting data transforms rows into columns, which is especially useful for reporting purposes. When dealing with a dynamic set of module completions—where the number and names of modules can change frequently—static SQL pivot queries aren't efficient. Instead, you can use dynamic SQL to create a flexible solution.

Step-by-Step Guide

Step 1: Understand Your Data

Let's assume you have a table named LearnerModules with the following schema:

[[See Video to Reveal this Text or Code Snippet]]

1 Math Completed
1 Science In Progress
2 Math Not Started
2 History Completed
3 Science Completed
3 Math In Progress

[[See Video to Reveal this Text or Code Snippet]]

Step 3: Create the Dynamic SQL Query

Next, build the dynamic SQL query string for pivoting:

[[See Video to Reveal this Text or Code Snippet]]

Step 4: Execute the Query

This dynamic query will pivot the module completion data into columns dynamically, and the result set will appear as follows:

[[See Video to Reveal this Text or Code Snippet]]

1 Completed In Progress
2 Not Started Completed
3 In Progress Completed

[[See Video to Reveal this Text or Code Snippet]]