title: Crosstab Query with Dynamic Columns in SQL Server 2005 up
body: <p><br /><br />
  I'm having a problem with Crosstab query in SQL Server. </p>
  
  <p>Suppose that I have data as below: </p>
  
  <pre><code>| ScoreID | StudentID |      Name |    Sex | SubjectName | Score |
  ------------------------------------------------------------------
  |       1 |         1 | Student A |   Male |           C |   100 |
  |       2 |         1 | Student A |   Male |         C++ |    40 |
  |       3 |         1 | Student A |   Male |     English |    60 |
  |       4 |         1 | Student A |   Male |    Database |    15 |
  |       5 |         1 | Student A |   Male |        Math |    50 |
  |       6 |         2 | Student B |   Male |           C |    77 |
  |       7 |         2 | Student B |   Male |         C++ |    12 |
  |       8 |         2 | Student B |   Male |     English |    56 |
  |       9 |         2 | Student B |   Male |    Database |    34 |
  |      10 |         2 | Student B |   Male |        Math |    76 |
  |      11 |         3 | Student C | Female |           C |    24 |
  |      12 |         3 | Student C | Female |         C++ |    10 |
  |      13 |         3 | Student C | Female |     English |    15 |
  |      14 |         3 | Student C | Female |    Database |    40 |
  |      15 |         3 | Student C | Female |        Math |    21 |
  |      16 |         4 | Student D | Female |           C |    17 |
  |      17 |         4 | Student D | Female |         C++ |    34 |
  |      18 |         4 | Student D | Female |     English |    24 |
  |      19 |         4 | Student D | Female |    Database |    56 |
  |      20 |         4 | Student D | Female |        Math |    43 |
  </code></pre>
  
  <p>I want to make query which show the result  as below: </p>
  
  <pre><code>| StuID| Name      | Sex    | C  | C++ | Eng | DB | Math | Total | Average |
  |  1   | Student A | Male   | 100|  40 | 60  | 15 |  50  |  265  |   54    |
  |  2   | Student B | Male   | 77 |  12 | 56  | 34 |  76  |  255  |   51    |
  |  3   | Student C | Female | 24 |  10 | 15  | 40 |  21  |  110  |   22    |
  |  4   | Student D | Female | 17 |  34 | 24  | 56 |  43  |  174  |   34.8  |
  </code></pre>
  
  <p>How could I query to show output like this? <br /><br />
  <strong>Note:</strong> </p>
  
  <p>Subject Name:</p>
  
  <ul>
  <li>C</li>
  <li>C++</li>
  <li>English</li>
  <li>Database</li>
  <li><p>Math</p>
  
  <p>will be changed depend on which subject student learn.</p></li>
  </ul>
  
  <p>Please go to <a href="http://sqlfiddle.com/#!6/2ba07/1" rel="noreferrer">http://sqlfiddle.com/#!6/2ba07/1</a> to test this query.</p>