SQL Server 2008拆分重复Cust_No的联系人姓名并转置为多列

核心思路 #

先给每个客户的联系人分配唯一序号，再通过条件聚合将多行数据转置为目标列，同时拆分全名得到名和姓。

分步实现 #

1. 拆分姓名并添加联系人序号
   用ROW_NUMBER()给每个cust_no下的联系人分配序号（可根据实际需求调整排序字段），同时通过CHARINDEX和SUBSTRING拆分出FirstName和LastName：

   WITH ContactCTE AS (
       SELECT 
           cust_no,
           -- 拆分名（假设全名仅含一个空格分隔）
           SUBSTRING(contact_name, 1, CHARINDEX(' ', contact_name) - 1) AS FirstName,
           -- 拆分姓
           SUBSTRING(contact_name, CHARINDEX(' ', contact_name) + 1, LEN(contact_name)) AS LastName,
           -- 按客户分组生成联系人序号
           ROW_NUMBER() OVER(PARTITION BY cust_no ORDER BY contact_name) AS ContactSeq
       FROM YourTableName
   )
   

   若存在全名含多个空格的情况（比如中间名），可改用以下逻辑取最后一个空格后的内容作为姓：

   -- 处理含中间名的全名
   SUBSTRING(contact_name, 1, LEN(contact_name) - CHARINDEX(' ', REVERSE(contact_name))) AS FirstName,
   SUBSTRING(contact_name, LEN(contact_name) - CHARINDEX(' ', REVERSE(contact_name)) + 2, LEN(contact_name)) AS LastName
   

2. 条件聚合转置为目标列
   基于上面的CTE，用CASE+MAX组合将不同序号的联系人转成对应的FirstNameN和LastNameN列：

   SELECT 
       cust_no,
       MAX(CASE WHEN ContactSeq = 1 THEN FirstName END) AS FirstName1,
       MAX(CASE WHEN ContactSeq = 1 THEN LastName END) AS LastName1,
       MAX(CASE WHEN ContactSeq = 2 THEN FirstName END) AS FirstName2,
       MAX(CASE WHEN ContactSeq = 2 THEN LastName END) AS LastName2
       -- 若有更多联系人，继续添加对应的CASE分支
   FROM ContactCTE
   GROUP BY cust_no
   

扩展处理（联系人数量不固定场景） #

如果每个客户的联系人数量不确定，需要动态生成对应列，可使用动态SQL实现：

DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX)

-- 动态生成所有需要的列名
SELECT @cols = STUFF((
    SELECT ', MAX(CASE WHEN ContactSeq = ' + CAST(ContactSeq AS NVARCHAR) + ' THEN FirstName END) AS FirstName' + CAST(ContactSeq AS NVARCHAR)
           + ', MAX(CASE WHEN ContactSeq = ' + CAST(ContactSeq AS NVARCHAR) + ' THEN LastName END) AS LastName' + CAST(ContactSeq AS NVARCHAR)
    FROM (SELECT DISTINCT ContactSeq FROM (
            SELECT ROW_NUMBER() OVER(PARTITION BY cust_no ORDER BY contact_name) AS ContactSeq 
            FROM YourTableName
          ) t) seq
    ORDER BY ContactSeq
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '')

-- 拼接并执行动态SQL
SET @sql = '
WITH ContactCTE AS (
    SELECT 
        cust_no,
        SUBSTRING(contact_name, 1, CHARINDEX('' '', contact_name) - 1) AS FirstName,
        SUBSTRING(contact_name, CHARINDEX('' '', contact_name) + 1, LEN(contact_name)) AS LastName,
        ROW_NUMBER() OVER(PARTITION BY cust_no ORDER BY contact_name) AS ContactSeq
    FROM YourTableName
)
SELECT cust_no, ' + @cols + '
FROM ContactCTE
GROUP BY cust_no'

EXEC sp_executesql @sql

注意事项 #

• 替换YourTableName为实际表名
• 若存在全名不含空格的情况，需添加CASE判断避免报错，比如：

  CASE WHEN CHARINDEX(' ', contact_name) > 0 THEN SUBSTRING(...) ELSE contact_name END AS FirstName
  

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