SQL Server-从名称列表中获取首字母缩写

Question

我有一个表，其中包含分配给某个项目的员工列表。有点像这样：

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

我必须转换Employees列以获得名字的首字母和完整的姓氏。结果应该是这样的：

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

当值包含多个名称时会出现问题。对此可以做些什么？

Answer 1

PostgreSQL解决方案。假设一个单元格中的多个名称始终由逗号和后面的空格分隔，则可以

1将所有多名称单元格展开为单独的行
2将具有名称的列拆分为表示名字和姓氏的单独列
3从名字中提取第一个字母，并将其与姓氏连接

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 |

如果你想把单元格中的多个名字放回去，你可以在最后一步中加总它们

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 |

但话说回来，这一切都取决于你的表中干净一致的数据