How to create email addresses using SQL - duplicate names-CodePudding

I have a table with the first_name and last_name of the employees of a company. So I tried to do a simple query by concatenating the names and adding "@mycompany.com".

SELECT first_name || last_name || '@mycompany.com' AS employee_email
FROM employee

My result was:

"[email protected]"
"[email protected]"
"[email protected]"
"[email protected]"
"[email protected]"

However, I don't know how to handle cases where I will have duplicate names.

For instance, my base might have two people named John Smith, so I should have one with the email address "[email protected]" and the other one with the email address "[email protected]", and so on.

CodePudding user response：

You can generate a running number using the row_number window function:

SELECT first_name || 
       last_name || 
       CASE ROW_NUMBER() OVER (PARTITION BY first_name, last_name 
                               ORDER BY     1)
            WHEN 1 THEN ''
            ELSE ROW_NUMBER() OVER (PARTITION BY first_name, last_name 
                                    ORDER BY     1)::varchar
       END ||
       '@mycompany.com' AS employee_email
FROM   employee

or, more elegantly, using a CTE so you don't have to repeat the row_number expression:

WITH e AS (
  SELECT first_name, 
         last_name, 
         ROW_NUMBER() OVER (PARTITION BY first_name, last_name ORDER BY 1) AS rn
  FROM   employee
)
SELECT first_name ||  
       last_name ||  
       CASE rn WHEN 1 THEN '' ELSE rn::varchar END || 
       '@mycompany.com' AS employee_email
FROM   e