update duplicates in a column

• montecarlo2079

  SSC-Addicted

  Points: 403

  More actions

  August 13, 2015 at 2:19 pm

  #302017

  How do I append an accumulating number to duplicates in a column? I would prefer to update the table

  for example

  Name

  John

  John

  Tom

  Bob

  Bob

  Bob

  Mike

  to look like

  Name

  John

  John1

  Tom

  Bob

  Bob1

  Bob2

  Mike

  Thanks in advance!

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  August 13, 2015 at 2:41 pm

  #1819903

  Something like this should work. Please notice how I posted ddl and sample data in a consumable format so this is easy to work with. You should do this on future questions. 😀

  if OBJECT_ID('tempdb..#Something') is not null

  drop table #Something

  create table #Something

  (

  MyName varchar(10)

  )

  insert #Something

  select 'John' union all

  select 'John' union all

  select 'Tom' union all

  select 'Bob' union all

  select 'Bob' union all

  select 'Bob' union all

  select 'Mike';

  with MyCTE as

  (

  select *

  , ROW_NUMBER() over (partition by MyName order by MyName) - 1 as RowNum

  from #Something

  )

  update MyCTE

  set MyName = MyName + case when RowNum = 0 then '' else CAST(RowNum as varchar(2)) end

  select * from #Something

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/