Dhairya Lakhera Dhairya Lakhera - 3 months ago 12
SQL Question

multiple record in a single row

I have post graduation degree records of students in my database. student may have only one post graduation degree, some students may have more than one post graduation degree.

rollno | pgdegree | score
--------------------------
0001 | 41 | 56
0002 | 42 | 78
0002 | 49 | 75
0003 | 48 | 77


Here roll no. 0002 is more than one time and roll no. 0001,0003 are only one time.

i want my desired output as :

rollno | pgdegree1 | score1 | pgdegree2 | score2
------------------------------------------------
0001 | 41 | 56 | |
0002 | 42 | 78 | 49 | 75
0003 | 48 | 77 | |



Note : in my database any student can have one or two post gradation only. Not more than two PG degree.

Answer

Here is another solution using ROW_NUMBER() and conditional aggregation to save some unnecessary SELECTs :

SELECT s.rollno,
       MAX(CASE WHEN s.rnk = 1 THEN s.pgdegree END) AS pgdegree1, 
       MAX(CASE WHEN s.rnk = 1 THEN s.score    END) AS score1, 
       MAX(CASE WHEN s.rnk = 2 THEN s.pgdegree END) AS pgdegree2, 
       MAX(CASE WHEN s.rnk = 2 THEN s.score    END) AS score2
FROM
(
    SELECT t.*,
           ROW_NUMBER() OVER (PARTITION BY t.rollno ORDER BY t.pgdegree, t.score) AS rnk
    FROM YourTable t
) s
GROUP BY s.rollno
Comments