title: How to concatenate from multiple rows, then group and count, in SQL Server
body: <p>I am trying to get from this</p>
  
  <p>tPeople<br>
  Name<br>
   Alice<br>
   Bob</p>
  
  <p>tAnimals<br>
  Name Animal<br>
   Alice Cat<br>
   Alice Cat<br>
   Bob Horse<br>
   Bob Dog  </p>
  
  <p>To this</p>
  
  <p>Alice "Cat*2"<br>
  Bob "Horse, Dog"</p>
  
  <p>So far I have an intermediate table grouping and counting animals</p>
  
  <p>tGrouped
  Name Animal Tally<br>
  Alice Cat 2<br>
  Bob Dog 1<br>
  Bob Horse 1  </p>
  
  <p>And if I run</p>
  
  <pre><code>SELECT Name, Animals=STUFF((
      SELECT N', '+animal+'*'+tally FROM tgrouped as g
      WHERE g.Name = p.Name
      FOR XML PATH(''), TYPE).value(N'.[1]', N'varchar(max)'), 1, 2, N'')
  FROM tpeople as p
  </code></pre>
  
  <p>I get<br>
  Name Animals<br>
  Alice Cat*2<br>
  Bob Dog*1, Horse*1</p>
  
  <p>Is there any way to skip the intermediate table? And any way to not show the tally when it is 1?</p>