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?

Do you have a column indicating which is the first, second ... grade? — juergen d, Oct 29 '13 at 16:47
@juergend If I understand the question properly, I believe he wants to select a preset number of random rows (4 in this example) to sample. — pseudocoder, Oct 29 '13 at 16:48
Have a look at [Convert row data to column in SQL Server](http://stackoverflow.com/questions/17076089/convert-row-data-to-column-in-sql-server) — huMpty duMpty, Oct 29 '13 at 16:53
Pivot is a bit complicated... I have achieved the same result before using a temp table... are you open to a temp table solution? — logixologist, Oct 29 '13 at 16:53
For this example, up to 4 columns of sample row values with NULL values of some columns for students with less than 4 scores. And there's no extra column indicating the order of a grade record. — draca, Oct 29 '13 at 16:54
I considered PIVOT but doesnt that require explicit column names to be found in the row data? a temp table would make it more like a procedure rather than a set based SQL statement, but that may have to be considered if no set based tricks are available — draca, Oct 29 '13 at 16:57

Taryn · Answer 1 · 2013-10-29T17:20:08.720

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

Federico · Answer 2 · 2013-10-29T17:42:51.063

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

Very good. Do you happen to know how easily this technique can be applied to Oracle? — draca, Oct 29 '13 at 17:09
@Federico How is using **cursors** a good idea? This answer is not really scalable. — Kermit, Oct 29 '13 at 17:18
@Federico Please check your query syntax it doesn't compile -- http://sqlfiddle.com/#!3/73156/10 — Taryn, Oct 29 '13 at 17:21
You cannot reference the `Row` column in the filter as it doesn't exist yet — billinkc, Oct 29 '13 at 17:22
Swing and a miss, strike 2 "Incorrect syntax near ' as GRADE'." — billinkc, Oct 29 '13 at 17:41

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

2 Answers2