By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table employee (name varchar(20), surname varchar(20), Employee_ID int)
insert into employee values('John', 'Doe', 00123)
1 rows affected
insert into employee values('John', 'Doe', 12123)
1 rows affected
insert into employee values('John', 'Doe', 98123)
1 rows affected
insert into employee values('Ann', 'Doe', 00124)
1 rows affected
insert into employee values('Peter', 'Doe', 00125)
1 rows affected
select case when row_number() over(partition by name, surname order by Employee_ID) > 1
then (name + '.'
+ surname
+ right(Employee_ID,3)
+ cast(row_number() over(partition by name, surname order by Employee_ID)-1 as varchar)
+ 'email.com')
else (name + '.'
+ surname
+ right(Employee_ID,3)
+ 'email.com')
end
from employee
| (No column name) |
|---|
| Ann.Doe124email.com |
| John.Doe123email.com |
| John.Doe1231email.com |
| John.Doe1232email.com |
| Peter.Doe125email.com |
select (name + '.'
+ surname
+ right(Employee_ID,3)
+ cast(row_number() over(partition by name, surname order by Employee_ID) as varchar)
+ 'email.com')
from employee
| (No column name) |
|---|
| Ann.Doe1241email.com |
| John.Doe1231email.com |
| John.Doe1232email.com |
| John.Doe1233email.com |
| Peter.Doe1251email.com |