Select items by column value

Question

I have a table in SQL used to store result like so

Result
---------------
ID | DateCreated
1  | 2014-10-10

The Items under the result above

ResultItems
---------------
ResultID | StudentID | SubjID    | Test1 | Test2 | Exam
1        | 1         | 1         | 7     | 7     | 30
1        | 2         | 1         | 8     | 8     | 35
1        | 1         | 2         | 5     | 5     | 45
1        | 2         | 2         | 6     | 6     | 40

I need to select from this tables so that each subject is in its own column, with the score of each subject summed under it

Result items

Result Output
---------------
StudentID| SubjID-1  | SubjID-2
1        | 44        | 55      
2        | 51        | 52      

I did try quiet some queries, such as this one below, which didn't give the result I needed

SELECT r.*,
       ri.StudentID,
       ri.Test1,
       ri.Test2,
       ri.Exam,
       ( ri.Test1+ ri.Test2 + ri.Exam )  Total
  FROM Result r
       LEFT JOIN ResultItems ri
              ON ri.ResultID = r.id
 WHERE ri.Test1 <> '-' 
       AND
       ri.Test2 <> '-' 
       AND
       ri.exam <> '-';

How can I amend this query?

Edit

I read about Pivot and saw this question SQL - columns for different categories, in which case the names/id of the subject has to be know before hand, which would not work for my case

Answer 1

Solution for SQL Server

All you need is called Pivot.

CREATE TABLE #ResultItems
(
    ResultID INT,
    StudentID INT,
    SubjID INT,
    Test1 INT,
    Test2 INT,
    Exam INT
)


INSERT INTO #ResultItems (ResultID, StudentID, SubjID, Test1, Test2, Exam)
VALUES(1, 1 , 1 , 7, 7, 30),
(1, 2 , 1 , 8, 8, 35),
(1, 1 , 2 , 5, 5, 45),
(1, 2 , 2 , 6, 6, 40)

SELECT StudentId, [1], [2]
FROM (
    SELECT StudentId, SubjID, Test1 + Test2 + Exam AS TmpSum
    FROM #ResultItems
) AS DT
PIVOT(SUM(TmpSum) FOR SubjID IN ([1], [2])) AS PT

DROP TABLE #ResultItems 

SQLite solution

Use CASE

SELECT StudentId, SUM(Subj1) AS Subj1, SUM(Subj2) As Subj2
FROM (
SELECT t1.StudentId, CASE WHEN SubjID = 1 THEN Test1 + Test2 + Exam ELSE 0 END AS Subj1,
        CASE WHEN SubjID = 2 THEN Test1 + Test2 + Exam ELSE 0 END  AS Subj2
    FROM #ResultItems AS t1
) AS T
GROUP BY T.StudentID 

or subqueries:

SELECT t1.StudentId, (SELECT Test1 + Test2 + Exam FROM #ResultItems WHERE StudentID = t1.StudentID AND SubjID = 1) AS Subj1,
        (SELECT Test1 + Test2 + Exam FROM #ResultItems WHERE StudentID = t1.StudentID AND SubjID = 2) AS Subj2
    FROM #ResultItems AS t1
GROUP BY t1.StudentID