SQL remove duplicated rows with different values but keep and show them in single row

Question

There are 8000 rows including students name, course and grades. There are 4 courses in total so it means for each student there is maximum 4 rows. So I would like to create a table containing distinct student name and show different grades in the same row as below:

Many thanks.

PS. I noticed from your initial responses that it is not an easy task. So can I have table showing only students with more than one grade as I am not interested in students with only one grade? like this:

I think generating an exact table like that could be tough. Do you really need this exact output? — Tim Biegeleisen, Jun 12 '18 at 13:25
If you wanted something similar to that output you could investigate a pivot table, with column headings for Maths, English and IT with the respective values underneath. https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server — JonTout, Jun 12 '18 at 13:32

score 3 · Answer 1 · answered Jun 12 '18 at 13:27

You can do conditional aggregation :

select name,
       max(case when seq = 1 then Course end) as Course1,
       max(case when seq = 1 then Grade end) as Course1Grade, 
       max(case when seq = 2 then Course end) as Course2,
       max(case when seq = 2 then Grade end) as Course2Grade,
       . . . 
from (select *, row_number() over (partition by name order by course) as seq
      from table
     ) t
group by name;

score 1 · Accepted Answer · answered Jun 13 '18 at 09:04

This will surely work just need to add another join as of another course

SELECT n.name, N.course, N.grade, E.course, E.grade, I.course, I.grade
FROM (
    SELECT DISTICT name
    FROM STUDENT
    ) N
LEFT JOIN (
    SELECT name, course, grade
    WHERE course = MATH
    ) M
    ON (N.name = M.name)
LEFT JOIN (
    SELECT name, course, grade
    WHERE course = ENGLISH
    ) E
    ON (N.name = E.name)
LEFT JOIN (
    SELECT name, course, grade
    WHERE course = IT
    ) I
    ON (N.name = I.name)

hope this helps..

SQL remove duplicated rows with different values but keep and show them in single row

2 Answers2