CODEWITHSUNDEEP
sqlpostgresqlpivot
Sana
Sana

Reputation: 563

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

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:

enter image description here

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: enter image description here

Upvotes: 1

Views: 80

Answers (2)

kiran gadhe
kiran gadhe

Reputation: 743

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..

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

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;

Upvotes: 3

Related Questions