SQL aggregation without min and max
I am 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 I really want:
StudentName Course1(180) Course2(181)
Harry Potter John Wayne Tiffany Williams
John Williams Robert Smith Bob Adams
I tried this query:
Select StudentName, Min(InstructorName) as Course1, Max(InstructorName) as
Course2 from CoursesTbl
Group By StudentName
It is now clear to me what I need to group by student name. But using Min and Max breaks the instructor's 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.
Therefore, it does not display the instructors in the correct order.
Can anyone suggest how this can be fixed?
+3
source to share
1 answer
You can use conditional aggregation with the CASE statement along with an aggregate function to PIVOT 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 can 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 .
+4
source to share