title: SQL Server get distinct counts of name by each ID
body: <p>I have a dataset like :</p>
  
  <pre><code>ID  NAME
  1   Aaron
  2   Theon 
  3   Jon Snow
  4   Jon Snow
  4   Dany
  5   Arya
  5   Robert
  5   Tyrion
  </code></pre>
  
  <p>I need to add a new column to this that shows the output based on the number of distinct names per ID. So expected output would be:</p>
  
  <pre><code>ID  NAME  Mapping
  1   Aaron   1
  2   Theon   1
  3   Jon Snow 1
  4   Jon Snow 2
  4   Dany     2   
  5   Arya     3
  5   Robert   3
  5   Tyrion   3
  </code></pre>
  
  <p>I am confused about how to achieve this since I have tried a case statement where count(distinct(name)) does not return the right values. </p>