Let's create a table:
Create Table School (
StudentId int,
Course varchar(50),
Semester varchar(10),
Primary Key(StudentId, Semester));
Insert into School Values(1234, 'Math', 'Fall');
Insert into School Values (1234, 'Science', 'Spring');
Both rows are for a student with ID:1234. If you would like to combine these rows into one, you could do by different ways: 1.) do max for each column with Group by clause, 2.) do the inner join with same table, 3.) rank by semester and then pivot based on the rank. If you have one row data and another row null then you can do max and group by with common Id.
I am going to show you the third one here: Rank by semester and then pivot on the rank:
Select StudentId,
Max(Case when rk = 1 then Course end) as Course1,
Max(Case when rk = 1 then Semester end) as Semester1,
Max(Case when rk = 2 then Course end) as Course2,
Max(Case when rk = 2 then Semester end) as Semester2
From
(Select StudentId, Course, Semester,
Row_Number() Over
(Partition by StudentId Order by Semester) as rk
(Partition by StudentId Order by Semester) as rk
From School) as A
Group by StudentId;
Output:
Happy Programming!!