Projecting Rows into Columns

Say we have some data in different rows of a column that we would like to project in different columns. For example we have this:

and would like to transform into this:


To do the projection we can use Pivot feature:

SELECT StudentId, DisciplineId, [1] AS Course1, [2] AS Course2, [3] AS Course3, [4] AS Course4, [5] AS Course5
FROM 
(
 SELECT  StudentId, DisciplineId, CompletionDate, CourseId
 FROM TrainingDetails
) AS T1
PIVOT 
( 
 MAX (CompletionDate) FOR CourseId IN ([1], [2], [3], [4], [5])
) AS T2

Pivot will project the rows into columns. It will also automatically apply grouping to the rest of the columns. So it is important to only feed the query with same columns that will be used in the Select result. In this example, we narrow down the source to only have columns that will be used in query (StudentId, DisciplineId, CompletionDate and CourseId) from other unrelated columns in the source (TrainingDetails table). If there is any extra column, the grouping will not be done correctly.

However, we can also achieve the same result with a more standard query:

SELECT StudentId, DisciplineId
, MAX(CASE WHEN CourseId = 1 THEN CompletionDate END) AS CompletionDateCourse1
, MAX(CASE WHEN CourseId = 2 THEN CompletionDate END) AS CompletionDateCourse2
, MAX(CASE WHEN CourseId = 3 THEN CompletionDate END) AS CompletionDateCourse3
, MAX(CASE WHEN CourseId = 4 THEN CompletionDate END) AS CompletionDateCourse4
, MAX(CASE WHEN CourseId = 5 THEN CompletionDate END) AS CompletionDateCourse5
FROM TrainingDetails
GROUP BY StudentId, DisciplineId