Oracle 10g – Query to Concatenate Columns if Record Duplicate

concatoracleoracle-10g

My table has two column that is id and name. I want to do concatenate the columns if the if more than two names are same like:

id                                                                    name
1                                                                     John
2                                                                     David
3                                                                     Zed
4                                                                     John
5                                                                     David
6                                                                     John
7                                                                     David
8                                                                     John
9                                                                     John

Now I want my output that if name is repeated than the first record will remain as it is and other will be concatenated by id like

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

Can anyone please help…Thank you

Best Answer

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) > 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) > 1 then id || ' ' || name
    else name
  end as name
from t order by id;
Related Question