对三列组执行UNPIVOT后出现重复值，该SQL写法是否正确？

• 连续UNPIVOT产生笛卡尔积：每次UNPIVOT都会基于前一步的结果集展开，比如先把部门拆成N行，再对这N行拆邮箱得到NM行，再拆电话得到NM*K行，直接导致大量重复数据。
• WHERE条件逻辑完全错误：RIGHT(Department,1) = RIGHT(Email,1)这种匹配没有任何业务依据，部门值是Y，邮箱是字符串，两者最后一位大概率不相等，会错误过滤有效数据，或留下错误匹配结果。

正确实现方案 #

方案一：拆分各维度后按序号关联 #

先分别将部门、邮箱、电话拆分为带序号的独立数据集，再通过EmpID和序号（比如DepartmentA对应序号1，Email1对应序号1）进行关联，同时保留单独存在的邮箱/部门/电话行：

WITH DeptCTE AS (
    SELECT 
        ID,
        EmpID,
        Department,
        DepartmentType,
        CAST(RIGHT(DepartmentType, 1) AS INT) AS Seq
    FROM Company
    UNPIVOT (
        Department FOR DepartmentType IN (DepartmentA, DepartmentB, DepartmentC)
    ) p
    WHERE Department = 'Y'
),
EmailCTE AS (
    SELECT 
        ID,
        EmpID,
        Email,
        EmailType,
        CAST(RIGHT(EmailType, 1) AS INT) AS Seq
    FROM Company
    UNPIVOT (
        Email FOR EmailType IN (Email1, Email2, Email3)
    ) p
    WHERE Email IS NOT NULL
),
PhoneCTE AS (
    SELECT 
        ID,
        EmpID,
        Phone,
        PhoneType,
        CAST(RIGHT(PhoneType, 1) AS INT) AS Seq
    FROM Company
    UNPIVOT (
        Phone FOR PhoneType IN (Phone1, Phone2, Phone3)
    ) p
    WHERE Phone IS NOT NULL
)
SELECT 
    ROW_NUMBER() OVER(ORDER BY COALESCE(d.EmpID, e.EmpID, p.EmpID), COALESCE(d.Seq, e.Seq, p.Seq)) AS ID,
    COALESCE(d.EmpID, e.EmpID, p.EmpID) AS EmpID,
    d.Department,
    d.DepartmentType,
    e.Email,
    e.EmailType,
    p.Phone,
    p.PhoneType
FROM DeptCTE d
FULL JOIN EmailCTE e ON d.EmpID = e.EmpID AND d.Seq = e.Seq
FULL JOIN PhoneCTE p ON COALESCE(d.EmpID, e.EmpID) = p.EmpID AND COALESCE(d.Seq, e.Seq) = p.Seq
UNION ALL
SELECT 
    ROW_NUMBER() OVER(ORDER BY EmpID, Seq) + (SELECT COUNT(*) FROM DeptCTE FULL JOIN EmailCTE ON DeptCTE.EmpID=EmailCTE.EmpID AND DeptCTE.Seq=EmailCTE.Seq) AS ID,
    EmpID,
    NULL AS Department,
    NULL AS DepartmentType,
    Email,
    EmailType,
    NULL AS Phone,
    NULL AS PhoneType
FROM EmailCTE e
WHERE NOT EXISTS (SELECT 1 FROM DeptCTE d WHERE d.EmpID = e.EmpID AND d.Seq = e.Seq)
AND NOT EXISTS (SELECT 1 FROM PhoneCTE p WHERE p.EmpID = e.EmpID AND p.Seq = e.Seq)
ORDER BY ID;

方案二：用UNION ALL直接构造目标行 #

针对原表每行，按序号（1/2/3）组合对应的部门、邮箱、电话项，生成符合要求的行，同时过滤全空行：

WITH CompanyRows AS (
    SELECT 
        ID,
        EmpID,
        DepartmentA, 'DepartmentA' AS DeptA_Type,
        DepartmentB, 'DepartmentB' AS DeptB_Type,
        DepartmentC, 'DepartmentC' AS DeptC_Type,
        Email1, 'Email1' AS Email1_Type,
        Email2, 'Email2' AS Email2_Type,
        Email3, 'Email3' AS Email3_Type,
        Phone1, 'Phone1' AS Phone1_Type,
        Phone2, 'Phone2' AS Phone2_Type,
        Phone3, 'Phone3' AS Phone3_Type
    FROM Company
)
SELECT 
    ROW_NUMBER() OVER(ORDER BY EmpID, Seq) AS ID,
    EmpID,
    Department,
    DepartmentType,
    Email,
    EmailType,
    Phone,
    PhoneType
FROM (
    SELECT 
        EmpID,
        DepartmentA AS Department, DeptA_Type AS DepartmentType,
        Email1 AS Email, Email1_Type AS EmailType,
        Phone1 AS Phone, Phone1_Type AS PhoneType,
        1 AS Seq
    FROM CompanyRows
    WHERE DepartmentA = 'Y' OR Email1 IS NOT NULL OR Phone1 IS NOT NULL
    UNION ALL
    SELECT 
        EmpID,
        DepartmentB AS Department, DeptB_Type AS DepartmentType,
        Email2 AS Email, Email2_Type AS EmailType,
        Phone2 AS Phone, Phone2_Type AS PhoneType,
        2 AS Seq
    FROM CompanyRows
    WHERE DepartmentB = 'Y' OR Email2 IS NOT NULL OR Phone2 IS NOT NULL
    UNION ALL
    SELECT 
        EmpID,
        DepartmentC AS Department, DeptC_Type AS DepartmentType,
        Email3 AS Email, Email3_Type AS EmailType,
        Phone3 AS Phone, Phone3_Type AS PhoneType,
        3 AS Seq
    FROM CompanyRows
    WHERE DepartmentC = 'Y' OR Email3 IS NOT NULL OR Phone3 IS NOT NULL
) t
WHERE NOT (Department IS NULL AND Email IS NULL AND Phone IS NULL)
ORDER BY ID;

内容的提问来源于stack exchange，提问作者Anh Hung Tran