Count number occurrences of a String in a table and report it in a new column

Input

Name
A
A
B
B
B
B
C
B
C
A
D

Required Output

Name    Occurance
A   1
A   2
B   1
B   2
B   3
B   4
C   1
B   5
C   2
A   3
D   1

Please Note - I have more than 100000 unique entries in the name column and hence I cannot hardcode name in the code

Solution

The expected result shows (for example) that A occurs 3 times and gets numbered 1, 2, 3 even though they don't all occur together. A single row_number() will suffice: SQL Fiddle

CREATE TABLE Table1 (ID int identity(1,1) primary key, [Name] varchar(1));   
INSERT INTO Table1 ([Name])
VALUES     ('A'),('A'),('B'),('B'),('B'),('B'),('C'),('B'),('C'),('A'),('D');

Query 1:

select
       name
     , row_number() over (partition by name order by id) as Occurance
from table1
order by id

As tables are considered unordered sets, to reproduce the sequence shown in the expected result there would have to be some other column(s) for that. I have assumed an identity column ID for that.

Results:

| name | Occurance |
|------|-----------|
|    A |         1 |
|    A |         2 |
|    B |         1 |
|    B |         2 |
|    B |         3 |
|    B |         4 |
|    C |         1 |
|    B |         5 |
|    C |         2 |
|    A |         3 |
|    D |         1 |

If you aren't familiar with these, I recommend you do become so as they are extremely handy: