How can I join a table to itself having it look like it's three tables?-CodePudding

Honestly, I don't know how to join a table to itself to solve this problem.

I have a table that stored it's record in the format below:

I want to query the table and display it's record in this format:

This is what I have tried so far

Select f.score as first_term, s.score as second_term, t.score as term_tetm from table f left join table s left join table t using (studentid) where studentid = 001 group by subject

CodePudding user response：

You can get the result by grouping on the subject name, but then you will have to use an aggregate function. Here is an example:

CREATE TABLE #StudentGrades  
(
    [SUBJECT]       VARCHAR(50),  
    [STUDENT_ID]    VARCHAR(3),  
    [SCORE]         INT,  
    [TERM]          VARCHAR(50)
)

INSERT INTO #StudentGrades ([SUBJECT], [STUDENT_ID],[SCORE],[TERM])
VALUES  ('English', '001', 50, '1st_Term'),
('Mathematics', '001', 40, '1st_Term'),
('French', '001', 60, '1st_Term'),
('English', '001', 60, '2nd_Term'),
('Mathematics', '001', 50, '2nd_Term'),
('French', '001', 50, '2nd_Term'),
('Computer', '001', 70, '2nd_Term'),
('English', '001', 65, '3rd_Term'),
('Mathematics', '001', 60, '3rd_Term'),
('French', '001', 70, '3rd_Term'),
('Computer', '001', 80, '3rd_Term')


SELECT  [SUBJECT],
MAX(CASE WHEN [TERM] = '1st_Term' THEN [SCORE] END) AS '1ST_TERM', 
MAX(CASE WHEN [TERM] = '2nd_Term' THEN [SCORE] END) AS '2ND_TERM', 
MAX(CASE WHEN [TERM] = '3rd_Term' THEN [SCORE] END) AS '3RD_TERM'
FROM #StudentGrades
GROUP BY [SUBJECT]
ORDER BY [SUBJECT]

CodePudding user response：

This is a simple solution but it works. Selecting student, subject and then sub-select for each of the terms. Finally, we DISTINCT it because there will be repetitions due to same subject on more than 1 term.

SELECT 
    DISTINCT 
    student_id, 
    `subject`, 
    (SELECT score FROM test AS t2 WHERE t2.student_id = t1.student_id AND t1.subject=t2.subject AND t2.term=1) AS term1,
    (SELECT score FROM test AS t2 WHERE t2.student_id = t1.student_id AND t1.subject=t2.subject AND t2.term=2) AS term2,
    (SELECT score FROM test AS t2 WHERE t2.student_id = t1.student_id AND t1.subject=t2.subject AND t2.term=3) AS term3
    
FROM 
    test AS t1