Appemd code at the end of duplicate name

• karthik M

  One Orange Chip

  Points: 29139

  More actions

  August 5, 2009 at 2:28 am

  #156059

  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

• savee

  Say Hey Kid

  Points: 691

  More actions

  August 5, 2009 at 2:52 am

  #1034658

  Try with this :

  select ename + ' ' + (case when ((select count(*) from emp where ename=e.ename) > 1 ) then ecode else '' end) as ename from emp e

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  August 5, 2009 at 2:54 am

  #1034659

  SELECT ename + CASE WHEN COUNT(*) OVER(PARTITION BY ename) > 1 THEN ' ' + ecode ELSE '' END

  FROM emp

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537