title: "Collapse" table to eliminate null values
body: <p>I have a product that allows creation of custom fields in the GUI.  I created three fields: System Ship Date, System Warranty End Date, and System Warranty Notes.  Sadly, the system populates these as rows, not columns.</p>
  
  <p>Example:</p>
  
  <pre><code>Machine_GroupID      | agentGuid | fieldName                |  fieldValue
  -----------------------------------------------------------------------------
  systemABC.companyABC | 57537921  | System Ship Date         | 1/1/1900
  systemABC.companyABC | 57537921  | System Warranty End Date | 1/1/1950
  systemABC.companyABC | 57537921  | System Warranty Notes    | Test notes
  system123.companyABC | 48394844  | System Ship Date         | 1/1/1900
  system123.companyABC | 48394844  | System Warranty End Date | 1/1/1950
  system123.companyABC | 48394844  | System Warranty Notes    | Test notes
  systemXYZ.companyABC | 99948403  | System Ship Date         | 1/1/1900
  systemXYZ.companyABC | 99948403  | System Warranty End Date | 1/1/1950
  systemXYZ.companyABC | 99948403  | System Warranty Notes    | Test notes
  </code></pre>
  
  <p>I need to return System Ship Date as a column, System Warranty End Date as a column, and System Warranty Notes as a column.  If I use a CASE WHEN statement:</p>
  
  <pre><code>CASE WHEN sim.fieldName = 'System Ship Date' 
       THEN sim.fieldValue END AS 'System Ship Date',
  CASE WHEN sim.fieldName = 'System Warranty End Date' 
       THEN sim.fieldValue END AS 'System Warranty End Date',
  CASE WHEN sim.fieldName = 'System Warranty Notes' 
       THEN sim.fieldValue END AS 'System Warranty Notes'
  </code></pre>
  
  <p><strong>The data appears thusly on a per-computer basis:</strong></p>
  
  <p><strong>header1 header2 header3</strong><br>
  data1 NULL NULL<br>
  NULL data2 NULL<br>
  NULL NULL data3</p>
  
  <p><strong>I need it to appear like this:</strong></p>
  
  <p><strong>header1 header2 header3</strong><br>
  data1 data2 data3</p>
  
  <p>I thought I could somehow use MAX() but I'm a novice and it's beyond me.  Alternatively I know I could create a temporary table but I don't know that either.  I've searched extensively online and on stack, but that's as far as I could get is the CASE WHEN statement.</p>