CODEWITHSUNDEEP
sqlsql-server
draca
draca

Reputation: 1399

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

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?

Upvotes: 5

Views: 668

Answers (2)

Federico
Federico

Reputation: 479

I propose something like this. The only restrict is that you can't display examples 1 to n, you are limited to determined number.

SELECT DISTINCT student, 
(
    select top 1 grade from
    (select grade, ROW_NUMBER() OVER(ORDER BY grade DESC) AS Row from grades g2 where g.student = g2.student) a where a.Row = 1
) AS GRADE1  ,
(
    select top 1 grade from
    (select grade, ROW_NUMBER() OVER(ORDER BY grade DESC) AS Row from grades g2 where g.student = g2.student) a where a.Row = 2
) AS GRADE2,
(
    select top 1 grade from
    (select grade, ROW_NUMBER() OVER(ORDER BY grade DESC) AS Row from grades g2 where g.student = g2.student) a where a.Row = 3
) AS GRADE3,
(
    select top 1 grade from
    (select grade, ROW_NUMBER() OVER(ORDER BY grade DESC) AS Row from grades g2 where g.student = g2.student) a where a.Row = 4
) AS GRADE4
from grades g

With cursors. In this solution you will had 1 to n results 

DECLARE @grade int
DECLARE @n int

DECLARE @sql varchar(max)

DECLARE _cursor CURSOR FOR 
     SELECT grade
     FROM grade
     WHERE student like 'XXX'

OPEN _cursor
FETCH NEXT FROM _cursor INTO @grade
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @sql like ''
    begin
         set @sql = 'SELECT ' + 'XXX' + ' AS Student '
    end
    set @sql = @sql + ',' + @grade ' as GRADE'+  @n
    set @n = @n + 1
END

CLOSE _cursor
DEALLOCATE _cursor

exec(@sql)

This output will be

STUDENT    GRADE1  GRADE2   GRADE3  GRADE4
John       94      87       83      76

Upvotes: 0

Taryn
Taryn

Reputation: 247760

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

Upvotes: 6

Related Questions