无法为拆分后的姓名字符串构建Pivot查询的技术求助

我有两种格式的姓名字符串：

• 「名字 姓氏」格式，比如 John Doe
• 「姓氏, 名字」格式，比如 Doe, John

我需要把这类字符串转换成如下格式的表格：

我尝试了以下SQL查询，但它无法正常工作：

declare @query nvarchar(100) = 'Doe, John'
select FirstName, LastName
from (
    select (select count(*) from dbo.SplitString(@query, ',') T1 where [Item]=T1.Item) value, Item
    from dbo.SplitString(@query, ',')
) d
pivot (
    max(value) for Item in (Firstname, LastName)
) piv;

我的SplitString()函数拆分后的结果如下（以输入Doe, John为例）：

我对Pivot的使用不太熟悉，希望能得到帮助解决这个问题，谢谢！

问题分析 #

你的Pivot写法逻辑有误：你用count(*)作为聚合值，并且试图直接用Item的内容来匹配FirstName和LastName列，这显然行不通——因为拆分后的Item是具体的姓名内容，不是用来标识列名的标签。我们需要的是根据姓名的格式或拆分后的位置来区分名字和姓氏，而不是靠内容匹配。

解决方案 #

这里提供两种可行的方法，你可以根据需求选择：

方法1：无需拆分函数，直接用字符串函数处理（推荐）

这种方法更高效，不需要依赖自定义的SplitString函数，直接通过字符串操作完成转换：

DECLARE @query NVARCHAR(100) = 'Doe, John'; -- 可替换为 'John Doe' 测试不同格式

SELECT
    -- 判断是否有逗号，提取FirstName
    CASE 
        WHEN CHARINDEX(',', @query) > 0 
            THEN LTRIM(RIGHT(@query, LEN(@query) - CHARINDEX(',', @query)))
            ELSE LEFT(@query, CHARINDEX(' ', @query) - 1)
    END AS FirstName,
    -- 提取LastName
    CASE
        WHEN CHARINDEX(',', @query) > 0
            THEN LTRIM(LEFT(@query, CHARINDEX(',', @query) - 1))
            ELSE RIGHT(@query, LEN(@query) - CHARINDEX(' ', @query))
    END AS LastName;

方法2：结合你的SplitString函数实现

如果你必须使用现有的SplitString函数，可以给拆分后的行添加序号，再根据序号和原格式来映射FirstName和LastName：

DECLARE @query NVARCHAR(100) = 'Doe, John';

WITH SplitNames AS (
    SELECT 
        Item = LTRIM(Item), -- 去除拆分后可能存在的前导空格
        RowNum = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM dbo.SplitString(@query, CASE WHEN CHARINDEX(',', @query) > 0 THEN ',' ELSE ' ' END)
)
SELECT
    FirstName = CASE 
                    WHEN CHARINDEX(',', @query) > 0 THEN MAX(CASE WHEN RowNum = 2 THEN Item END)
                    ELSE MAX(CASE WHEN RowNum = 1 THEN Item END)
                END,
    LastName = CASE 
                    WHEN CHARINDEX(',', @query) > 0 THEN MAX(CASE WHEN RowNum = 1 THEN Item END)
                    ELSE MAX(CASE WHEN RowNum = 2 THEN Item END)
                END
FROM SplitNames;

说明 #

• 方法1通过CHARINDEX检测字符串中是否包含逗号，以此区分两种姓名格式：有逗号时按逗号拆分，无逗号时按空格拆分，LTRIM用来处理拆分后可能残留的空格。
• 方法2先根据原字符串格式选择对应的拆分符（逗号或空格），给拆分后的每条记录添加行号，再根据行号和原格式来分别提取FirstName和LastName。

内容的提问来源于stack exchange，提问作者Aishwarya Shiva