title: Group by first column with results from second column as xml
body: <p>So suppose I have a table defined as this:</p>
  
  <pre><code>CREATE TABLE #TmpExampleTable
  (   
      id int identity(0,1),
      method_id int, 
      entity_id int
  )
  </code></pre>
  
  <p>and with sample data added to the table a select * returns this:</p>
  
  <pre><code>id  method_id   entity_id
  0   105         39
  1   105         43
  2   9           92
  3   9           572
  4   9           1014
  5   9           1427
  6   9           1959
  7   9           2266
  </code></pre>
  
  <p>I would like to transform the results returned from the following select statement:</p>
  
  <pre><code>select method_id,entity_id from #TmpExampleTable
  
  method_id   entity_id
  105         39
  105         43
  9           92
  9           572
  9           1014
  9           1427
  9           1959
  9           2266
  </code></pre>
  
  <p>into something like this:</p>
  
  <pre><code>method_id   entities
  105         &lt;Entities&gt;&lt;Entity id=39 /&gt;&lt;Entity id=43 /&gt;&lt;/Entities&gt;
  9           &lt;Entities&gt;&lt;Entity id=572 /&gt;&lt;Entity id=1014 /&gt;&lt;Entity id=1427 /&gt;&lt;Entity id=1959 /&gt;&lt;Entity id=2266 /&gt;&lt;/Entities&gt;
  </code></pre>
  
  <p>Is this even possible? I've tried various combinations of CTEs and FOR XML statements but cannot get this to format in the way that I want.</p>