append code to the duplicate name

• karthik M

  One Orange Chip

  Points: 29139

  More actions

  August 5, 2009 at 2:24 am

  #74084

  All,

  I want to add code at the end of duplicate names.

  create table emp

  (

  ecode char(5),

  ename varchar(30)

  )

  insert into emp values ('A0001','Marshall')

  insert into emp values ('A0002','Tony')

  insert into emp values ('A0003','Lorenz')

  insert into emp values ('A0004','Kavi')

  insert into emp values ('A0005','Kavi')

  insert into emp values ('A0006','Brandon')

  insert into emp values ('A0007','Jack')

  insert into emp values ('A0008','Steve')

  insert into emp values ('A0009','Rock')

  insert into emp values ('A0010','Ranjit')

  insert into emp values ('A0011','Rajesh')

  insert into emp values ('A0012','Rajesh')

  Expected output:

  Name

  Marshall

  Tony

  Lorenz

  Kavi A0004

  Kavi A0005

  Brandon

  Jack

  Steve

  Rock

  Ranjit

  Rajesh A0011

  Rajesh A0012

  If the name is not a duplicate one, then no need to add the code.

  karthik

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  August 5, 2009 at 3:52 am

  #1034673

  Try this:

  select e.ecode, ename = e.ename + CASE WHEN counts.cnt > 1 THEN ' ' + e.ecode ELSE '' END

  from emp as e

  inner join (

  select ename, count(*) as cnt

  from emp

  group by ename

  ) as counts

  on e.ename = counts.ename

  -- Gianluca Sartori