title: How to perform a dynamic pivot in T-SQL on a subset of columns
body: <p>Software used: SQL Server 2014 Management Studio to query a standard 64-bit SQL Server.</p>
  <p>I'm trying to create a query that generates a set of patient assessment results across multiple patients / assessments, and to put the results of each assessment on one row. This would suggest the use of pivoting of some sort.</p>
  <p>Each assessment consists of a number of tests, each of which has a type, a name and a score. It is possible that we may want to add more tests to an assessment at some point, which would suggest the use of dynamic pivots.</p>
  <p>At the moment, my query essentially looks like this:</p>
  <pre><code>SELECT TOP 20000
      P.PatientId,
      P.LastName,
      P.FirstName,
      FORMAT(P.DateOfBirth, 'dd/MM/yyyy') as 'DateOfBirth',
      FORMAT(A.TreatmentDate, 'dd/MM/yyyy') as 'AssmentDate',
      A.TestType,
      A.TestName,
      A.TestScore
  FROM
      Patient P
  INNER JOIN 
      Assessment A ON (A.PatientSerialNumber = P.PatientSerialNumber)
  INNER JOIN 
      AssessmentHeader AH ON (AH.AssessmentSerialNumber = A.AssessmentSerialNumber 
                              AND AH.PatientSerialNumber = P.PatientSerialNumber)
  WHERE
      A.ValidEntryIndicator = 'Y'
      AND AH.ValidEntryIndicator = 'Y'
  ORDER BY
      P.PatientId, T.TreatmentDate
  </code></pre>
  <p>My results essentially look like this (there are actually a lot more test types and names than shown here - these are just for illustration):</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>PatientId</th>
  <th>LastName</th>
  <th>FirstName</th>
  <th>DateOfBirth</th>
  <th>AssmentDate</th>
  <th>TestType</th>
  <th>TestName</th>
  <th>TestScore</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>AB1234</td>
  <td>PATIENT</td>
  <td>Test</td>
  <td>1/1/2000</td>
  <td>1/1/2020</td>
  <td>Renal</td>
  <td>Urgency</td>
  <td>0</td>
  </tr>
  <tr>
  <td>AB1234</td>
  <td>PATIENT</td>
  <td>Test</td>
  <td>1/1/2000</td>
  <td>1/1/2020</td>
  <td>Renal</td>
  <td>Retention</td>
  <td>1</td>
  </tr>
  <tr>
  <td>AB1234</td>
  <td>PATIENT</td>
  <td>Test</td>
  <td>1/1/2000</td>
  <td>1/1/2020</td>
  <td>GI</td>
  <td>Proctitis</td>
  <td>2</td>
  </tr>
  <tr>
  <td>AB1234</td>
  <td>PATIENT</td>
  <td>Test</td>
  <td>1/1/2000</td>
  <td>1/1/2020</td>
  <td>GI</td>
  <td>Diarrhea</td>
  <td>3</td>
  </tr>
  <tr>
  <td>AB1234</td>
  <td>PATIENT</td>
  <td>Test</td>
  <td>1/1/2000</td>
  <td>6/6/2021</td>
  <td>Renal</td>
  <td>Urgency</td>
  <td>1</td>
  </tr>
  <tr>
  <td>AB1234</td>
  <td>PATIENT</td>
  <td>Test</td>
  <td>1/1/2000</td>
  <td>6/6/2021</td>
  <td>Renal</td>
  <td>Retention</td>
  <td>0</td>
  </tr>
  <tr>
  <td>AB1234</td>
  <td>PATIENT</td>
  <td>Test</td>
  <td>1/1/2000</td>
  <td>6/6/2021</td>
  <td>GI</td>
  <td>Proctitis</td>
  <td>1</td>
  </tr>
  <tr>
  <td>AB1234</td>
  <td>PATIENT</td>
  <td>Test</td>
  <td>1/1/2000</td>
  <td>6/6/2021</td>
  <td>GI</td>
  <td>Diarrhea</td>
  <td>2</td>
  </tr>
  <tr>
  <td>YZ6789</td>
  <td>PATIENT2</td>
  <td>Test2</td>
  <td>1/1/1999</td>
  <td>7/7/2020</td>
  <td>Renal</td>
  <td>Urgency</td>
  <td>2</td>
  </tr>
  <tr>
  <td>YZ6789</td>
  <td>PATIENT2</td>
  <td>Test2</td>
  <td>1/1/1999</td>
  <td>7/7/2020</td>
  <td>Renal</td>
  <td>Retention</td>
  <td>5</td>
  </tr>
  <tr>
  <td>YZ6789</td>
  <td>PATIENT2</td>
  <td>Test2</td>
  <td>1/1/1999</td>
  <td>7/7/2020</td>
  <td>GI</td>
  <td>Proctitis</td>
  <td>3</td>
  </tr>
  <tr>
  <td>YZ6789</td>
  <td>PATIENT2</td>
  <td>Test2</td>
  <td>1/1/1999</td>
  <td>7/7/2020</td>
  <td>GI</td>
  <td>Diarrhea</td>
  <td>1</td>
  </tr>
  <tr>
  <td>YZ6789</td>
  <td>PATIENT2</td>
  <td>Test2</td>
  <td>1/1/1999</td>
  <td>6/7/2021</td>
  <td>Renal</td>
  <td>Urgency</td>
  <td>2</td>
  </tr>
  <tr>
  <td>YZ6789</td>
  <td>PATIENT2</td>
  <td>Test2</td>
  <td>1/1/1999</td>
  <td>6/7/2021</td>
  <td>Renal</td>
  <td>Retention</td>
  <td>9</td>
  </tr>
  <tr>
  <td>YZ6789</td>
  <td>PATIENT2</td>
  <td>Test2</td>
  <td>1/1/1999</td>
  <td>6/7/2021</td>
  <td>GI</td>
  <td>Proctitis</td>
  <td>4</td>
  </tr>
  <tr>
  <td>YZ6789</td>
  <td>PATIENT2</td>
  <td>Test2</td>
  <td>1/1/1999</td>
  <td>6/7/2021</td>
  <td>GI</td>
  <td>Diarrhea</td>
  <td>5</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>What I would like is this:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>PatientId</th>
  <th>LastName</th>
  <th>FirstName</th>
  <th>DateOfBirth</th>
  <th>AssmentDate</th>
  <th>Renal-Urgency</th>
  <th>Renal-Retention</th>
  <th>GI-Proctitis</th>
  <th>GI-Diarrhea</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>AB1234</td>
  <td>PATIENT</td>
  <td>Test</td>
  <td>1/1/2000</td>
  <td>1/1/2020</td>
  <td>0</td>
  <td>1</td>
  <td>2</td>
  <td>3</td>
  </tr>
  <tr>
  <td>AB1234</td>
  <td>PATIENT</td>
  <td>Test</td>
  <td>1/1/2000</td>
  <td>6/6/2021</td>
  <td>1</td>
  <td>0</td>
  <td>1</td>
  <td>2</td>
  </tr>
  <tr>
  <td>YZ6789</td>
  <td>PATIENT2</td>
  <td>Test2</td>
  <td>1/1/1999</td>
  <td>7/7/2020</td>
  <td>2</td>
  <td>5</td>
  <td>3</td>
  <td>1</td>
  </tr>
  <tr>
  <td>YZ6789</td>
  <td>PATIENT2</td>
  <td>Test2</td>
  <td>1/1/1999</td>
  <td>6/7/2021</td>
  <td>2</td>
  <td>9</td>
  <td>4</td>
  <td>5</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I've tried to follow various online resources and SO question/answers (<a href="https://stackoverflow.com/questions/12210692/t-sql-dynamic-pivot">this one</a> looked the most promising), but I just can't seem to get the methods shown in these to work with my query (frustratingly)</p>
  <p>Can anyone help me out?</p>