title: Returning multiple values from SELECT nested within CASE
body: <p>I'm trying to return multiple values in a single column which are linked to a position number (1 through 6), but only those values which are of a certain position in each case. Basically I have 6 columns I want to create, 1 for each position, and return all of the result values associated with that position number. I created 6 Nested Case statements to try to get these results:</p>
  
  <pre><code>(CASE WHEN tbl_TestWells.well_result &lt;&gt; NULL
  THEN 
  (SELECT tbl_TestWells.well_result
  FROM tbl_TestWells
  WHERE tbl_TestWells.Well_Index = 1)
  ELSE NULL END) AS  Well_1_Graded_Result,
  </code></pre>
  
  <p>The problem is that they only return NULL values in the way it's set up. If I take out the case then I get the Error Message "subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, &lt;, &lt;= , >, >= or when the subquery is used as an expression." </p>
  
  <p>EDIT: The full code is part a view so I've simplified it to what's pertinent</p>
  
  <pre><code>SELECT DISTINCT
  --Other Columns being selected
  
  (CASE WHEN tbl_TestWells.well_result &lt;&gt; NULL
  THEN 
  (SELECT tbl_TestWells.well_result
  FROM tbl_TestWells
  WHERE tbl_TestWells.Well_Index = 1)
  ELSE NULL END) AS  Well_1_Graded_Result,
  
  --The other five well results are set up in the exact same format as above, just replacing 1 with the corresponding number
  
  FROM tbl_TestCartridges
  
  JOIN tbl_Tests ON
  tbl_Tests.test_uid = tbl_TestCartridges.test_uid
  
  JOIN tbl_Programs ON
  tbl_Programs.program_uid = tbl_Tests.program_uid
  
  JOIN tbl_Cartridges ON
  tbl_Cartridges.system_uid = tbl_Programs.system_uid
  
  JOIN tbl_TestWells ON
  tbl_TestWells.test_cartridge_uid = tbl_TestCartridges.test_cartridge_uid
  
  JOIN tbl_TestSamples ON
  tbl_TestSamples.test_well_uid = tbl_TestWells.test_well_uid 
  
  ORDER BY tbl_TestCartridges.barcode
  
  ; 
  </code></pre>