How to have query return samples of row values as columns?

Question

Given a table named "grades":

STUDENT  GRADE
john     94
john     76
john     83
john     87
john     90

I want a query to return a few examples of grades for each student, for example:

STUDENT    GRADE1  GRADE2   GRADE3  GRADE4
John       94      76       83      87

Notice there are only 4 sample grade columns returned but there are more than 4 grades for this student.

I only know how to return 2 example grades using the min() and max() functions in a GROUP BY clause:

select student, min(grade), max(grade) 
from grades
group by student

Are there any tricks or functions other than min/max that will enable display of more than only 2 grades using the GROUP BY clause?

I'd rather not write my own stored function to do this unless it were part of the query rather than stored in the database.

I'm thinking of more functions that returned other values from the recordset besides the MIN and MAX (like the second highest, 3rd highest, etc).

Ideas?

Answer 1

You can get the result by applying the row_number() function and then applying the PIVOT:

select student, 
  grade1 = [1], 
  grade2 = [2], 
  grade3 = [3], 
  grade4 = [4]
from
(
  select student, grade,
    row_number() over(partition by student
                      order by grade desc) seq
  from grades
) d
pivot
(
  max(grade)
  for seq in ([1], [2], [3], [4]) -- the # of grades you want returned
) piv;

See SQL Fiddle with Demo.

The new column names used in the PIVOT will be the number of grades that you want returned. The ORDER BY being used in the partition is by grade desc, but you could also look at using order by newid() to get a random result being returned.

This could also be accomplished using an aggregate function with a CASE expression:

select student,
  max(case when seq = 1 then grade end) grade1,
  max(case when seq = 2 then grade end) grade2,
  max(case when seq = 3 then grade end) grade3,
  max(case when seq = 4 then grade end) grade4
from
(
  select student, grade,
    row_number() over(partition by student
                      order by newid()) seq
  from grades
) d
group by student;

See SQL Fiddle with Demo