title: SQL server pivot multiple columns to multiple rows and including nulls
body: <p>I have the following table:</p>
  
  <pre><code>+----------------------------------------------------------------------+
  | Student   Class1    Class1_score  Class2   Class2_score   Class3.... |
  +----------------------------------------------------------------------+
  | Alex      English   70            Maths    100            NULL       |
  | John      Science   50            NULL     NULL           NULL       |
  | Bruce     Maths     50            Science  50             English    |
  +----------------------------------------------------------------------+
  </code></pre>
  
  <p>Which i want to pivot to something like the following:</p>
  
  <pre><code>+--------------------------+
  | Student   Class    Score |
  +--------------------------+
  | Alex      English   70   |
  | Alex      Maths     100  |
  | Alex      Science   NULL |
  | Alex      History   NULL |
  | John      English   NULL |
  +--------------------------+
  </code></pre>
  
  <p>Which includes NULL for classes that are not in the original table for that particular student (e.g. Science for Alex)</p>
  
  <p>How can I achieve this in SQL?</p>