title: Rename duplicate rows
body: <p>Here's a simplified example of my problem. I have a table where there's a "Name" column with duplicate entries:</p>
  
  <pre><code>ID    Name
  ---   ----
   1    AAA
   2    AAA
   3    AAA
   4    BBB
   5    CCC
   6    CCC
   7    DDD
   8    DDD
   9    DDD
  10    DDD
  </code></pre>
  
  <p>Doing a GROUP BY like <code>SELECT Name, COUNT(*) AS [Count] FROM Table GROUP BY Name</code> results in this:</p>
  
  <pre><code>Name  Count
  ----  -----
  AAA   3
  BBB   1
  CCC   2
  DDD   4
  </code></pre>
  
  <p>I'm only concerned about the duplicates, so I'll add a HAVING clause, <code>SELECT Name, COUNT(*) AS [Count] FROM Table GROUP BY Name HAVING COUNT(*) &gt; 1</code>:</p>
  
  <pre><code>Name  Count
  ----  -----
  AAA   3
  CCC   2
  DDD   4
  </code></pre>
  
  <p>Trivial so far, but now things get tricky: I need a query to get me all the duplicate records, but with a nice incrementing indicator added to the Name column. The result should look something like this:</p>
  
  <pre><code>ID    Name
  ---   --------
   1    AAA
   2    AAA (2)
   3    AAA (3)
   5    CCC 
   6    CCC (2)
   7    DDD 
   8    DDD (2)
   9    DDD (3)
  10    DDD (4)
  </code></pre>
  
  <p>Note row 4 with "BBB" is excluded, and the first duplicate keeps the original Name. </p>
  
  <p>Using an <code>EXISTS</code> statement gives me all the records I need, but how do I go about creating the new Name value?</p>
  
  <pre><code>SELECT * FROM Table AS T1 
  WHERE EXISTS (
      SELECT Name, COUNT(*) AS [Count] 
      FROM Table 
      GROUP BY Name 
      HAVING (COUNT(*) &gt; 1) AND (Name = T1.Name))
  ORDER BY Name
  </code></pre>
  
  <p>I need to create an UPDATE statement that will fix all the duplicates, i.e. change the Name as per this pattern.</p>
  
  <p>Update:
  Figured it out now. It was the <a href="http://msdn.microsoft.com/en-us/library/ms189461.aspx" rel="noreferrer">PARTITION BY</a> clause I was missing. </p>