title: Combining Rows in SQL Based on Common Values
body: <p>I have a list of states with a count being attached to each one. I would like to combine the rows that share a similar state, so that there is only 1 row per state and the counts are either the number or null.</p>
  
  <p><strong>Query</strong></p>
  
  <pre><code>select COALESCE(t1.State, t2.State, t3.State) AS 'State', t1.DMECount, t2.TransCount, t3.ProCount 
  from #TestTable t1
  FULL OUTER JOIN #TestTable2 t2 ON t1.State = t2.State
  FULL OUTER JOIN #TestTable3 t3 ON t2.State = t3.State
  ORDER BY 'State'
  </code></pre>
  
  <p><strong>Current Result</strong></p>
  
  <pre><code>State|DMECount|TransCount|ProCount
  MA   |19      |NULL      |NULL
  MA   |NULL    |NULL      |1
  MD   |NULL    |NULL      |1
  MD   |8       |NULL      |NULL
  MI   |1450    |1         |259
  MN   |21      |NULL      |NULL
  MN   |NULL    |NULL      |2
  </code></pre>
  
  <p><strong>Desired Result</strong></p>
  
  <pre><code>State|DMECount|TransCount|ProCount
  MA   |19      |NULL      |1
  MD   |8       |NULL      |1
  MI   |1450    |1         |259
  MN   |21      |NULL      |2
  </code></pre>
  
  <p>From other solutions I've looked at, it seems like grouping by the state and taking the MAX of the others may do what I need, but I cannot group by a field that has COALESCE being used on it. The COALESCE is being used so that way if one of the tables has a state that the others don't, the state column uses that value instead of NULL.</p>