SQL aggregation without min and max

Question

I'm relatively new to SQL. I currently have the following CoursesTbl

StudentName     CourseID     InstructorName
  Harry Potter     180        John Wayne
  Harry Potter     181        Tiffany Williams
  John Williams    180        Robert Smith
  John Williams    181        Bob Adams

Now what I really want is this:

StudentName     Course1(180)     Course2(181)
 Harry Potter   John Wayne      Tiffany Williams
 John Williams  Robert Smith    Bob Adams

I've tried this query:

Select StudentName, Min(InstructorName) as Course1, Max(InstructorName) as 
Course2 from CoursesTbl
Group By StudentName

Now it's clear to me that I need to group by the Student Name. But using Min and Max messes up the instructor order.

i.e. Min for Harry is John Wayne and Max is Tiffany Williams
Min for John Williams is Bob Adams and Max is Robert Smith.

So it does not display instructors in the correct order.

Can anyone please suggest how this could be fixed?

Answer 1

You can use conditional aggregation with a CASE statement along with an aggregate function to PIVOT the data into columns:

select 
  [StudentName],
  Course1 = max(case when CourseId = 180 then InstructorName end),
  Course2 = max(case when CourseId = 181 then InstructorName end)
from #Table1
group by StudentName

See Demo. You could also use the PIVOT function to get the result:

select 
  StudentName,
  Course1 = [180],
  Course2 = [181]
from
(
  select StudentName,
    CourseId,
    InstructorName
  from #Table1
) d
pivot
( 
  max(InstructorName)
  for CourseId in ([180], [181])
) piv

Another Demo.