How to return multiple row value as a single row with multiple column using sql

Question

There are three row in Static Table called 'SUBJECT'

SubjectID       SubjectCode
---------------------------
    1           MATHS
    2           SCIENCE
    3           ENGLISH
---------------------------

My application will put entry of each subject marks for corresponding students in another table called 'MARKS'

MarkID      StudentID       SubjectID       Marks
---------------------------------------------------
    1           1               1           90
    2           1               2           85
    3           1               3           80
    4           2               1           100
    5           2               2           70
    6           2               3           90

I am getting struggle to create a virtual table as like below:

StudentID       Maths       Science     English 
------------------------------------------------
    1           90          85          80
    2           100         70          90

I tried with the query

Select  MARKS.StudentID,
        CASE WHEN SUBJECTS.SubjectCode = 'MATHS' THEN MARKS.Marks END as Maths, 
        CASE WHEN SUBJECTS.SubjectCode = 'SCIENCE' THEN MARKS.Marks END as Science, 
        CASE WHEN SUBJECTS.SubjectCode = 'ENGLISH' THEN MARKS.Marks END as English, 
        FROM 
        MARKS 
        JOIN SUBJECTS on SUBJECTS.SubjectID = MARKS.SubjectID
        GROUP BY 
        MARKS.StudentID, SUBJECTS.SubjectCode, MARKS.Marks

But it returns,

StudentID       Maths       Science     English 
------------------------------------------------
    1           90          NULL        NULL
    1           NULL        85          NULL
    1           NULL        NULL        80
    2           100         NULL        NULL
    2           NULL        70          NULL
    2           NULL        NULL        90

Gordon Linoff · Accepted Answer · 2018-09-13 11:30:37Z

4

You are close. You just need aggregation:

SELECT MARKS.StudentID,
       MAX(CASE WHEN SUBJECTS.SubjectCode = 'MATHS' THEN MARKS.MARK END) as Maths, 
       MAX(CASE WHEN SUBJECTS.SubjectCode = 'SCIENCE' THEN MARKS.MARK END) as Science, 
       MAX(CASE WHEN SUBJECTS.SubjectCode = 'ENGLISH' THEN MARKS.MARK END) as English, 
FROM MARKS JOIN
     SUBJECTS 
     ON SUBJECTS.SubjectID = MARKS.SubjectID
GROUP BY MARKS.StudentID
ORDER BY MARKS.StudentID;

I would recommend that you use table aliases, so the query is easier to write and read:

SELECT m.StudentID,
       MAX(CASE WHEN s.SubjectCode = 'MATHS' THEN M.MARK END) as Maths, 
       MAX(CASE WHEN s.SubjectCode = 'SCIENCE' THEN M.MARK END) as Science, 
       MAX(CASE WHEN s.SubjectCode = 'ENGLISH' THEN M.MARK END) as English, 
FROM MARKS m JOIN
     SUBJECTS s
     ON s.SubjectID = m.SubjectID
GROUP BY m.StudentID
ORDER BY m.StudentID;

edited Sep 13, 2018 at 11:30

answered Sep 13, 2018 at 10:21

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Fahmi · Accepted Answer · 2018-09-13 10:23:21Z

3

Use group by and aggregation

Select  MARKS.StudentID,
        max(CASE WHEN SUBJECTS.SubjectCode = 'MATHS' THEN RS.AgentPercentage END) as Maths, 
        max(CASE WHEN SUBJECTS.SubjectCode = 'SCIENCE' THEN RS.AgentPercentage END) as Science, 
        max(CASE WHEN SUBJECTS.SubjectCode = 'ENGLISH' THEN RS.AgentPercentage END) as English, 
        FROM 
        MARKS 
        JOIN SUBJECTS on SUBJECTS.SubjectID = MARKS.SubjectID
group by MARKS.StudentID

answered Sep 13, 2018 at 10:23

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

3 Comments

jarlh Over a year ago

Same answer as Gordon's, just two minutes later.

Fahmi Over a year ago

yah @jarlh, i was typing and you know Gordon is faster than anyone, but if you've issues with my answer you can down vote me , no issue. But as it was not copy paste Gordon's answer so I'll not delete my answer

jarlh Over a year ago

Ha ha, yes, Gordon types very fast! (I've experience the same several times!)

Serkan Arslan · Accepted Answer · 2018-09-13 10:28:26Z

You can use Pivot, if you are using sql-server.

DECLARE @SUBJECT TABLE (SubjectID INT,  SubjectCode VARCHAR(10))
INSERT INTO @SUBJECT VALUES 
(1 ,'MATHS'),
(2 ,'SCIENCE'),
(3 ,'ENGLISH')

DECLARE @MARKS TABLE (MarkID INT, StudentID INT, SubjectID INT, Marks INT)
INSERT INTO @MARKS VALUES
(1 ,1, 1, 90 ),
(2 ,1, 2, 85 ),
(3 ,1, 3, 80 ),
(4 ,2, 1, 100),
(5 ,2, 2, 70 ),
(6 ,2, 3, 90 )

SELECT * FROM (
    SELECT M.StudentID, M.Marks, S.SubjectCode FROM @MARKS M 
        INNER JOIN @SUBJECT S ON S.SubjectID = M.SubjectID) SRC
PIVOT (MAX(Marks) FOR SubjectCode IN ([MATHS],[SCIENCE],[ENGLISH])) PVT

Result:

StudentID   MATHS       SCIENCE     ENGLISH
----------- ----------- ----------- -----------
1           90          85          80
2           100         70          90

Collectives™ on Stack Overflow

How to return multiple row value as a single row with multiple column using sql

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related