如何对含多组联系人信息的数据表进行部分转置？

需求说明 #

需要将包含多组联系人信息的宽表（同一客户的多个联系人信息存于同一行的不同列），转换为每个联系人占一行的窄表，最终表包含ClientCode、Telephone、Name三个字段，按ClientCode分组展示同一客户的所有联系人。

原始数据表 #

目标数据表 #

问题诊断 #

你尝试的SQL分别对Name和Telephone字段单独执行UNPIVOT后用UNION合并，这种方式会将姓名和电话拆分为独立的行，无法保证同一联系人的姓名与电话对应，因此输出结果不符合预期。

正确实现方案 #

方案1：UNION ALL 拼接（兼容多数数据库） #

这种方法直接将每组联系人的字段单独选取后拼接，逻辑简单，支持MySQL、SQL Server、Oracle等绝大多数数据库：

SELECT ClientCode, Telephone1 AS Telephone, Name1 AS Name
FROM -ORIGIN_TABLE-
WHERE Telephone1 IS NOT NULL OR Name1 IS NOT NULL -- 过滤空联系人记录
UNION ALL
SELECT ClientCode, Telephone2 AS Telephone, Name2 AS Name
FROM -ORIGIN_TABLE-
WHERE Telephone2 IS NOT NULL OR Name2 IS NOT NULL
UNION ALL
SELECT ClientCode, Telephone3 AS Telephone, Name3 AS Name
FROM -ORIGIN_TABLE-
WHERE Telephone3 IS NOT NULL OR Name3 IS NOT NULL
ORDER BY ClientCode;

方案2：SQL Server 专属 UNPIVOT 关联写法 #

如果使用SQL Server，可以通过标记联系人序号的方式，将姓名与电话关联后再转置：

SELECT ClientCode, Telephone, Name
FROM (
    SELECT ClientCode, 1 AS ContactSeq, Telephone1 AS Telephone, Name1 AS Name
    FROM -ORIGIN_TABLE-
    UNION ALL
    SELECT ClientCode, 2 AS ContactSeq, Telephone2 AS Telephone, Name2 AS Name
    FROM -ORIGIN_TABLE-
    UNION ALL
    SELECT ClientCode, 3 AS ContactSeq, Telephone3 AS Telephone, Name3 AS Name
    FROM -ORIGIN_TABLE-
) t
WHERE Telephone IS NOT NULL OR Name IS NOT NULL
ORDER BY ClientCode, ContactSeq;

也可以通过两次UNPIVOT后关联的方式实现：

WITH NameUnpivot AS (
    SELECT ClientCode, ContactTag, Name
    FROM (
        SELECT ClientCode, Name1, Name2, Name3
        FROM -ORIGIN_TABLE-
    ) p
    UNPIVOT (
        Name FOR ContactTag IN (Name1, Name2, Name3)
    ) unpvt
),
TelephoneUnpivot AS (
    SELECT ClientCode, ContactTag, Telephone
    FROM (
        SELECT ClientCode, Telephone1, Telephone2, Telephone3
        FROM -ORIGIN_TABLE-
    ) p
    UNPIVOT (
        Telephone FOR ContactTag IN (Telephone1, Telephone2, Telephone3)
    ) unpvt
)
SELECT 
    n.ClientCode, 
    t.Telephone, 
    n.Name
FROM NameUnpivot n
JOIN TelephoneUnpivot t 
    ON n.ClientCode = t.ClientCode 
    AND RIGHT(n.ContactTag,1) = RIGHT(t.ContactTag,1) -- 通过末尾数字关联同组联系人
WHERE n.Name IS NOT NULL OR t.Telephone IS NOT NULL
ORDER BY n.ClientCode;

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