query to concatenate columns if record duplicate
Use partition by on the name column, and count the rows based on the order of id-s. Where the row number of the actual row is greater than 1, concatenate id and name, else just use the name.

with t as (
select 1 as id, 'john' as name from dual union all
select 2, 'david' from dual union all
select 3, 'zed' from dual union all
select 4, 'john' from dual union all
select 5, 'david' from dual union all
select 6, 'john' from dual union all
select 7, 'david' from dual union all
select 8, 'john' from dual union all
select 9, 'john' from dual
)
select 
  id, 
  case 
    when row_number() over (partition by name order by id) &gt; 1 then id || ' ' || name
    else name
  end as name
from t order by id;

        ID NAME
---------- ----------------------------------------------
         1 john
         2 david
         3 zed
         4 4 john
         5 5 david
         6 6 john
         7 7 david
         8 8 john
         9 9 john

9 rows selected.


To treat john, JOHN, joHN, etc. equally, simply change the partition by column, e.g upper(name).

select 
  id, 
  case 
    when row_number() over (partition by upper(name) order by id) &gt; 1 then id || ' ' || name
    else name
  end as name
from t order by id;