Select from two tables, convert rows from one table into colums, Sql Server

Language: Layout:

create table Student  (
    StudentID    int
  , LastName   nvarchar(32)
  , FirstName  nvarchar(32)
  , MiddleName nvarchar(32)
  , IsActive   char(1)
  , Grade      int
  , SchoolName nvarchar(32)
  ); 
insert into dbo.Student values
 (1,'Pinciati','Donna','J','Y',10,'High')
,(2,'Hyde','Steven','K','Y',11,'Middle')
,(3,'Foreman','Eric','L','Y',10,'High')
,(4,'Kelso','Michael','G','Y',11,'High');

create table dbo.Alert (
    ID            int
  , StudentID     int
  , AlertType     nvarchar(64)
  , AlertComments nvarchar(64)
);
insert into dbo.Alert values
 (1,2,'Asthma','NULL')
,(2,2,'Allergy','Bee Sting')
,(3,3,'Allergy','Peanut')
,(4,4,'Medial Equipment','Crutches')
,(5,4,'Allergy','Bee Sting');

;with al as (
  select StudentId, AlertType, AlertComments
    , rn = row_number() over (
      partition by StudentId
      order by AlertType, AlertComments
      )
from dbo.Alert
)
select
    s.StudentID 
  , s.LastName  
  , s.FirstName 
  , s.MiddleName
  , s.IsActive  
  , s.Grade     
  , s.SchoolName
  , AlertType_1     = max(case when rn = 1 then al.AlertType else null end)
  , AlertComments_1 = max(case when rn = 1 then al.AlertComments else null end)
  , AlertType_2     = max(case when rn = 2 then al.AlertType else null end)
  , AlertComments_2 = max(case when rn = 2 then al.AlertComments else null end)
  , AlertType_3     = max(case when rn = 3 then al.AlertType else null end)
  , AlertComments_3 = max(case when rn = 3 then al.AlertComments else null end)
  from dbo.student as s
    left join al on s.StudentId = al.StudentId 
  group by 
    s.StudentID 
  , s.LastName  
  , s.FirstName 
  , s.MiddleName
  , s.IsActive  
  , s.Grade     
  , s.SchoolName

View schema
Execution time: 0,03 sec, rows selected: 4, rows affected: 9, absolute service time: 0,19 sec