没有min和max的SQL聚合。

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

我对SQL比较陌生。我目前有以下课程

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.

现在我明白了，我需要以学生的名字来分组。但是使用Min和Max会打乱指导书的顺序。

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.

也就是说，最小值是约翰·韦恩，Max是蒂凡尼·威廉姆斯，最小值是鲍勃·亚当斯，Max是罗伯特·史密斯。

So it does not display instructors in the correct order.

所以它不会以正确的顺序显示导师。

Can anyone please suggest how this could be fixed?

谁能告诉我这是怎么解决的吗?

1 个解决方案

#1

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

您可以使用条件聚合和CASE语句以及聚合函数将数据透视为列:

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.

另一个演示。

#1