Add repeat data (A) back from the number of growth (a-1,a-2 ... ）

database table Test

ID Auto-Growing primary key

Name nvarchar (10)

ID Name

1 A

2 A

3 A

4 B

5 B

6 C

7 C

Add -1,-2 after the duplicate name ....

As shown below

ID Name

1 A

2 A-1

3 A-2

4 B

5 B-1

6 C

7 C-1

Select id ,name = < Span style= "color: #808080;" >name + when no = 1 then else "-' rtrim ( - 1) end into #AA

From (Select *, no = row_number () over ( Partition by name order id ) from test ) t

Order by ID

Update test set name= (select name from #AA where id=test.id)

Add repeat data (A) back from the number of growth (a-1,a-2 ... ）