title: Query to return the lookup values for multiple columns in a table
body: <p>I have to query a legacy MySQL database and export the results as a CSV file. It is on a relatively locked-down system so the solution has to be SQL only.</p>
  <p>The problematic table, <code>dbo.NGSVariantACMG</code>, has 23 fields each relating to a category of evidence called PVS1, PS1, PS2 etc.  Each of these fields accepts a value of <code>null</code>, '3366', '3367', '3368', or '3369', which rate the strength of the evidence in that category for each row.  These values link to human readable descriptions, 'Very Strong', 'Strong', 'Moderate', 'Supporting' respectively, which can be looked up in another table called <code>item</code>, which has become something of a dumping ground for many different lookup values.</p>
  <p>Example of the data:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>PersonID</th>
  <th>PVS1</th>
  <th>PS1</th>
  <th>PS2</th>
  <th>...</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>null</td>
  <td>null</td>
  <td>'3367'</td>
  <td>...</td>
  </tr>
  <tr>
  <td>2</td>
  <td>'3368'</td>
  <td>'3366'</td>
  <td>null</td>
  <td>...</td>
  </tr>
  <tr>
  <td>3</td>
  <td>null</td>
  <td>'3369'</td>
  <td>'3369'</td>
  <td>...</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Rather than the arbitary 4 digit string I would like to export the data in a human readable format:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Person</th>
  <th>PVS1</th>
  <th>PS1</th>
  <th>PS2</th>
  <th>...</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>null</td>
  <td>null</td>
  <td>'Strong'</td>
  <td>...</td>
  </tr>
  <tr>
  <td>2</td>
  <td>'Moderate'</td>
  <td>'Very Strong'</td>
  <td>null</td>
  <td>...</td>
  </tr>
  <tr>
  <td>3</td>
  <td>null</td>
  <td>'Supporting'</td>
  <td>'Supporting'</td>
  <td>...</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>The query I wrote below works fine, however I'm sure there is a more elegant way of doing it.  Basically, I avoid using the <code>item</code> table to look up the relevant human readable description and use a conditional CASE statement for each field:</p>
  <pre class="lang-sql prettyprint-override"><code>CASE dbo.NGSVariantACMG.PVS1
      WHEN '3366' THEN 'Very Strong'
      WHEN '3367' THEN 'Strong'
      WHEN '3368' THEN 'Moderate'
      WHEN '3369' THEN 'Supporting'
  END AS PVS1
  </code></pre>
  <p>This worked well for when I only wanted to return values for one field, but the query isn't easily scalable as I need to repeat it for each of the fields included (See example query at bottom of page which runs to over 200 lines).  Am I missing something obvious?</p>
  <p>I have tried generating the same output using a LEFT JOIN between each field and the <code>item</code> table but this is equally verbose and less readable.  I have also tried putting the CASE statement in a function so that the query is more succinct, but I could not get my implementation to work.  Any pointers would be appreciated.</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT dbo.NGSVariant.NGSVariantID, dbo.NGSVariant.DateAdded, dbo.NGSVariant.ChrID, dbo.NGSVariant.Position_hg19, 
  dbo.NGSVariant.ref, dbo.NGSVariant.alt, dbo.Status.Status AS 'ACMG_Class',
  CASE dbo.NGSVariantACMG.PVS1
      WHEN '3366' THEN 'Very Strong'
      WHEN '3367' THEN 'Strong'
      WHEN '3368' THEN 'Moderate'
      WHEN '3369' THEN 'Supporting'
  END AS PVS1,
  dbo.NGSVariantACMG.PVS1_comment,
  CASE dbo.NGSVariantACMG.PS1
      WHEN '3366' THEN 'Very Strong'
      WHEN '3367' THEN 'Strong'
      WHEN '3368' THEN 'Moderate'
      WHEN '3369' THEN 'Supporting'
  END AS PS1,
  dbo.NGSVariantACMG.PS1_comment,
  CASE dbo.NGSVariantACMG.PS2
      WHEN '3366' THEN 'Very Strong'
      WHEN '3367' THEN 'Strong'
      WHEN '3368' THEN 'Moderate'
      WHEN '3369' THEN 'Supporting'
  END AS PS2,
  dbo.NGSVariantACMG.PS2_comment,
  CASE dbo.NGSVariantACMG.PS3
      WHEN '3366' THEN 'Very Strong'
      WHEN '3367' THEN 'Strong'
      WHEN '3368' THEN 'Moderate'
      WHEN '3369' THEN 'Supporting'
  END AS PS3,
  
  # Omitted 126 lines essential repeating the above code for 18 additional fields
  
  CASE dbo.NGSVariantACMG.BP7
      WHEN '3366' THEN 'Very Strong'
      WHEN '3367' THEN 'Strong'
      WHEN '3368' THEN 'Moderate'
      WHEN '3369' THEN 'Supporting'
  END AS BP7,
  dbo.NGSVariantACMG.BP7_comment
  FROM dbo.NGSVariant 
  LEFT JOIN dbo.Status 
  ON dbo.NGSVariant.Classification = dbo.Status.StatusID 
  LEFT JOIN dbo.NGSVariantACMG
  ON dbo.NGSVariant.NGSVariantID = dbo.NGSVariantACMG.NGSVariantACMGID
  WHERE Classification = 1202218788 OR Classification = 1202218783 OR Classification = 1202218781
  </code></pre>