title: how to combine the result of one column to another
body: <p>I am trying to create a student database but i am stuck up the given requirement.</p>
  
  <p>I was given to create 2 tables one with students register number , subjects ans total and another table with student_name , total, rank. i can understand that here we need to use the student name separately and combine it later but the part that got me struck in the student subjects may vary means if student no :1 is having 3 subjects means student no : 2 may have 2 and student no: 3 may have 5 and based on this we have to put ranking order</p>
  
  <pre><code>CREATE TABLE Student
  (StudentID int, StudentName varchar(6), Details varchar(1));    
  
  
  INSERT INTO Student
  (StudentID, StudentName, Details)
    VALUES
  (1, 'John', 'X'),
  (2, 'Paul', 'X'),
  (3, 'George', 'X'),
  (4, 'Paul', 'X');
  
  CREATE TABLE Subject
  (SubjectID varchar(1), SubjectName varchar(7));    
  
  INSERT INTO Subject
    (SubjectID, SubjectName)
  VALUES
  ('M', 'Math'),
  ('E', 'English'),
  ('H', 'History');
  
  CREATE TABLE Mark
  (StudentID int, SubjectID varchar(1), MarkRate int);    
  
  INSERT INTO Mark
   (StudentID, SubjectID, MarkRate)
  VALUES
  (1, 'M', 90),
  (1, 'E', 100),
  (2, 'M', 95),
  (2, 'E', 70),
  (3, 'E', 95),
  (3, 'H', 98),
  (4, 'H', 90),
  (4, 'E', 100);
  </code></pre>
  
  <p>I need 2 outputs 
  1st one is </p>
  
  <pre><code>ID  |subjects |marks
  ----------------------------------------------------------
  1     maths     98
  1     science   87
  1     social    88
  2     maths     87
  2     english   99
  3     maths     96
  3     evs       100
  3     social     88
  3     history    90
  </code></pre>
  
  <p>and the second table as</p>
  
  <pre><code>NO  |name |total|rank
  ----------------------------------------------------------
  1   xxx     123     1
  2   yyy     456     2
  3   zzz     789      3
  </code></pre>
  
  <p>I need output like this for n number of entries</p>