1

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

2

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;
1

I found by only using update you can also do this:

UPDATE concatenate c2
SET c2.payer_name = source_id ||' '|| payer_name
WHERE c2.rowid > 
(
    SELECT min(c1.rowid)
    FROM concatenate c1  
    WHERE upper(c1.payer_name)=upper(c2.payer_name)
);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.