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?
In SQL Server you can use the PIVOT function to transform the data from rows to columns: select Firstname, Amount, PostalCode, LastName, AccountNumber from ( select value, columnname from yourtable ) d pivot ( max(value) for columnname in (Firstname, Amount, PostalCode, LastName, AccountNumber) ) piv; See Demo.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With