title: SQL Pivot with dynamic columns with SUM, COUNT or Aggregate results
body: <p>I have a query that generates results for learners on a specific course showing whether or not they have completed a module. Learners can take different modules from each other.</p>
  
  <pre><code>declare @CourseID int = 9   
  SELECT LearnerID, UnitID, 
  CASE WHEN (SUM(Total - [Total Achieved])) = 0 THEN 'Yes' ELSE 'No' END 
  AS Completed FROM dbo.LMS_Achieved_Standards_Report 
  GROUP BY CourseID, LearnerID, UnitID having CourseID = @CourseID
  </code></pre>
  
  <p>The results look like this</p>
  
  <pre><code>LearnerID  UnitID  Completed
  15      15        Yes
  15      28        No
  28      28        Yes
  116     150       Yes
  79      12        No
  69      34        Yes
  69      15        No
  </code></pre>
  
  <p>I need it to look like this:</p>
  
  <pre><code>LearnerID     Unit 15  Unit 28  Unit 150  Unit 12  Unit 34
  15              Yes      No
  28                       Yes
  116                               Yes
  79                                          Yes
  69              No                                   Yes
  </code></pre>
  
  <p>The other factor as already stated is that they can all take different units so I can't create a PIVOT with set column headings.</p>
  
  <p>I have looked at other examples of PIVOT with dynamic columns, tried a whole bunch of examples and just cannot work this out.</p>
  
  <p>I would really appreciate some help?</p>
  
  <p>Thanks</p>