SQL Server - Getting initials from a list of names-CodePudding

I have a table containing a list of employees assigned to a certain project. A bit like this :

ID  Employees
122 Mark Doe
210 John Doe
212 Julie Doe, Yuri Doe

I have to transform the Employees column to get the initial of the first name and get the full last name. The result should look like this :

ID  Employees
122 M. Doe
210 J. Doe
212 J. Doe, Y. Doe

Problem occurs when the value contains more then one name. What can be done for this?

CodePudding user response：

Solution for PostgreSQL. Assuming that the multiple names in a cell are consistently separated by a comma and a following space you could

1 expand all multi name cells into separate rows
2 split the column with the names into separate columns representing first name and last name
3 extract first letter from the first name and join it with the last name

CREATE TABLE employees (id int, employee VARCHAR(50))
INSERT INTO employees (id, employee)
VALUES 
(122, 'Mark Doe'),
(210, 'John Doe'),
(212, 'Julie Doe, Yuri Doe');
 
 SELECT
 id
 ,left(split_part(firs_last_name,' ',1),1) || '. ' || split_part(firs_last_name,' ',2) 
 as  wanted_name
 ,split_part(firs_last_name,' ',1) as first_name
 ,split_part(firs_last_name,' ',2) as last_name
 FROM
   (
   Select 
   id, unnest(string_to_array(employee, ', ')) as firs_last_name
    FROM employees
   ) s

|  id | wanted_name | first_name | last_name |
|-----|-------------|------------|-----------|
| 122 |      M. Doe |       Mark |       Doe |
| 210 |      J. Doe |       John |       Doe |
| 212 |      J. Doe |      Julie |       Doe |
| 212 |      Y. Doe |       Yuri |       Doe |

if you want to have the multiple names in the cells back, you can aggraegate them in the final step

CREATE TABLE employees (id int, employee VARCHAR(50))
INSERT INTO employees (id, employee)
VALUES 
(122, 'Mark Doe'),
(210, 'John Doe'),
(212, 'Julie Doe, Yuri Doe');
 
 with transformed_names as 
 (SELECT
 id
 ,left(split_part(firs_last_name,' ',1),1) || '. ' || 
  split_part(firs_last_name,' ',2)  as  wanted_name
 ,split_part(firs_last_name,' ',1) as first_name
 ,split_part(firs_last_name,' ',2) as last_name
 FROM
   (
   Select 
   id ,unnest(string_to_array(employee, ', ')) as firs_last_name
   FROM employees
   ) s)

SELECT
id
, string_agg(wanted_name, ', ') as wanted_names  
from transformed_names
GROUP BY 
id

|  id |     string_agg |
|-----|----------------|
| 122 |         M. Doe |
| 210 |         J. Doe |
| 212 | J. Doe, Y. Doe |

but then again it is all depending on clean consistent data in your table