title: Dynamic SQL pivot
body: <p>I'm using the following SQL (with SQL Server 2016) to pivot a list of test results from our LMS (Moodle):</p>
  
  <pre><code>SELECT * 
  FROM 
      (SELECT
           u.firstname AS name,
           u.lastname AS last_name,
           u.idnumber AS id_number,
           gi.itemname AS exam_name,
           CAST(gg.finalgrade / gi.grademax * 100 AS integer) AS grade
       FROM  
           mdl_grade_grades gg 
       INNER JOIN 
           mdl_grade_items gi ON gg.itemid = gi.id 
       INNER JOIN 
           mdl_course c ON gi.courseid = c.id 
       INNER JOIN 
           mdl_user u ON gg.userid = u.id
       WHERE 
           (gi.itemname IS NOT NULL) 
           AND (gi.courseid = 123)) SOURCE
  PIVOT 
      (MAX(grade)
       FOR exam_name IN ([Exam ABC], [Exam DEF], [Exam GHI],
                         [Exam JKL], [Exam XYZ])
      ) PIVT
  ORDER BY 
      id_number
  </code></pre>
  
  <p>The result will be:</p>
  
  <pre><code>name    last name   id number   division    region      branch      Exam ABC    Exam DEF    Exam GHI    Exam JKL    Exam XYZ
  John    Tester      3343664     ABC         WEST RGN    A AGY       65                                  44
  Kenny   Quipton     4342423     DDA         CENTRAL     RGN FRN     88          66          90                      89
  Molefi  Manase      5456545     CCS         ABC RGN     XXX SOL     74          90          85          80          77
  </code></pre>
  
  <p>My only issue with this is that the exam names have to be hardcoded in the <code>FOR IN(...)</code> list, so each time something changes the list has to be manually updated.</p>
  
  <p>Can this SQL be rewritten using dynamic SQL?</p>
  
  <p>What is better - using <code>Stuff()</code> or <code>FOR XML PATH</code>, or something else?</p>
  
  <p>I can't use stored procedures (the application does not support them).</p>