title: How to nest logic in a case block of sql
body: <p>I have a table with containing test scores for students. There can be more than 1 score per test type. So student A, can have multiple scores for Test 1, one score for Test 2, etc.</p>
  
  <p>Basically, if a student has only test score for Test 1, I want to do x. And if a student has multiple test scores for Test 1, I want to do y. How can I accomplish this?</p>
  
  <p>My query so far is:</p>
  
  <pre><code>SELECT A.*
    ,T.TestType
    ,CASE
      WHEN T.TestType = 'test_1' THEN T.MessageLocation
    END AS Loc
  FROM #Person A
  LEFT JOIN #TestScores T ON A.TestID = T.ID
  </code></pre>
  
  <p>But what I want to do (pseudo-code) is:</p>
  
  <pre><code>SELECT A.*
    ,T.TestType
    ,CASE
      -- when count(test_1) = 1 THEN T.MessageLocation
      -- when count(test_1) &gt; 1 THEN
      -- for each row T.AltMessageLocation
    END AS Loc
  FROM #Person A
  LEFT JOIN #TestScores T ON A.TestID = T.ID
  </code></pre>