title: SQL Server : join table to itself to show data horizontally
body: <p>I have a table that contains students marks (StudentsID, Subject, Year, Mark).</p>
  
  <p>I want to display the Marks horizontally for the same Student, subject but different year.</p>
  
  <p>The problem I have is that not all subjects available in all years, I mean the student can take different subject every year. and I want to show them all in one table horizontally.</p>
  
  <p>Example:</p>
  
  <p><a href="https://i.stack.imgur.com/n1iil.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/n1iil.png" alt="image 1"></a></p>
  
  <p>I tried this query</p>
  
  <pre><code>SELECT TOP (100) PERCENT 
      dbo.New_Portal_OldMarks.StudentID, dbo.New_Portal_OldMarks.[Student Name], 
      dbo.New_Portal_OldMarks.SubjectName, dbo.New_Portal_OldMarks.AcademicYear, 
      dbo.New_Portal_OldMarks.Class, dbo.New_Portal_OldMarks.Term1, dbo.New_Portal_OldMarks.Term2, 
      dbo.New_Portal_OldMarks.Term3, dbo.New_Portal_OldMarks.Final, 
      New_Portal_OldMarks_1.AcademicYear AS AcademicYear2, 
      New_Portal_OldMarks_1.Class AS Class2, 
      New_Portal_OldMarks_1.Term1 AS [Term1-2], 
      New_Portal_OldMarks_1.Term2 AS [Term2-2], 
      New_Portal_OldMarks_1.Term3 AS [Term3-2], 
      New_Portal_OldMarks_1.Final AS [Final-2]
  FROM
      dbo.New_Portal_OldMarks 
  FULL OUTER JOIN
      dbo.New_Portal_OldMarks AS New_Portal_OldMarks_1 ON dbo.New_Portal_OldMarks.StudentID = New_Portal_OldMarks_1.StudentID 
                                                       AND dbo.New_Portal_OldMarks.SubjectName = New_Portal_OldMarks_1.SubjectName
  WHERE     
      (dbo.New_Portal_OldMarks.StudentID = 1004000)
  ORDER BY 
      AcademicYear2
  </code></pre>
  
  <p>I tried all join query (ex JOIN, LEFT JOIN, RIGHT JOIN, etc) but all of them return wrong results</p>