SQL按联系人合并多行数据为单行 PIVOT行转列实现问题求助

问题背景 #

需要整理旧系统中的数据，目前数据库里邮箱和手机号分别存储在不同行，测试表结构及测试数据如下：

CREATE TABLE #temptable ( [LookupCode] char(10), [Title] varchar(20), [FirstName] varchar(30), [MiddleName] varchar(16), [LastName] varchar(30), [Number] varchar(150), [EmailWeb] varchar(150), [TypeCode] char(3), [Primary] int )
INSERT INTO #temptable ([LookupCode], [Title], [FirstName], [MiddleName], [LastName], [Number], [EmailWeb], [TypeCode], [Primary])
VALUES
( 'ANDERSSO01', 'Miss', 'Jessie', '', 'Bloggs', '', 'abc@gmail.com', 'EM1', 1 ), 
( 'ANDERSSO01', 'Mr', 'Joe', '', 'Bloggs', '01363936541', '', 'RES', 0 ), 
( 'ANDERSSO01', 'Mr', 'Joe', '', 'Bloggs', '', 'xyz@gmail.com', 'EM1', 0 ), 
( 'ANDERSSO01', 'Miss', 'Jessie', '', 'Bloggs', '073321663', '', 'RES', 1 )

SELECT * FROM #temptable t

DROP TABLE #temptable

测试数据样例：

需求说明 #

• 最终输出每个联系人仅占1行，手机号和邮箱展示在同一行
• TypeCode='EM1'对应邮箱，TypeCode='RES'对应住宅电话，已提前过滤无效的ACT类型TypeCode
• 同一个LookupCode下存在多个联系人，之前用子查询仅对单条记录生效，尝试PIVOT实现结果不符合预期

已尝试的PIVOT实现代码 #

WITH
    cte
AS  (
        SELECT
                c.LookupCode
            , cn.LkPrefix 'Title'
            , cn.FirstName
            , cn.MiddleName
            , cn.LastName
              , CAST(cn2.Number AS VARCHAR(150)) 'Number'
              , CAST(cn2.EmailWeb AS VARCHAR(150)) 'EmailWeb'
              , cn2.TypeCode
              , cn2.TypeCode 'TypeCode2'
              , c.UniqEntity
              , cn2.UniqContactName
              , IIF(c.UniqContactNamePrimary = cn2.UniqContactName, 1, 0) "Primary"
        FROM
                dbo.Client        c
            LEFT OUTER JOIN
                dbo.ContactNumber cn2           
                    ON cn2.UniqEntity = c.UniqEntity --AND cn2.UniqContactName = c.UniqContactNamePrimary
            LEFT OUTER JOIN dbo.ContactName cn ON c.UniqEntity = cn.UniqEntity AND cn2.UniqContactName = cn.UniqContactName
        WHERE
                c.LookupCode = @LookupCode
    )
    select RES 'Home', MOB 'Mobile',  EM1 'Email', [Primary], FirstName, LastName
from
(
  select *
  from cte
) d
pivot
(
  max(Number)
  for TypeCode in (RES, MOB)
) piv
pivot
(
  max(EmailWeb)
  for TypeCode2 in (EM1)
) piv2

原有PIVOT输出结果：

问题原因&解决方案 #

原有PIVOT结果异常的核心原因是源数据包含了过多维度字段，没有按联系人维度做预分组，导致聚合时拆分出多余空行。改用条件聚合的写法更简洁，也能精准实现需求：

针对测试表的实现代码 #

SELECT
    LookupCode,
    Title,
    FirstName,
    MiddleName,
    LastName,
    -- 优先取Primary=1的住宅电话，无主记录则取任意存在的记录
    COALESCE(MAX(CASE WHEN TypeCode = 'RES' AND [Primary] = 1 THEN Number END), MAX(CASE WHEN TypeCode = 'RES' THEN Number END)) AS HomePhone,
    -- 优先取Primary=1的邮箱，无主记录则取任意存在的记录
    COALESCE(MAX(CASE WHEN TypeCode = 'EM1' AND [Primary] = 1 THEN EmailWeb END), MAX(CASE WHEN TypeCode = 'EM1' THEN EmailWeb END)) AS Email
FROM #temptable
GROUP BY LookupCode, Title, FirstName, MiddleName, LastName

适配业务表的调整代码 #

基于你现有的CTE逻辑，替换后续的PIVOT部分为条件聚合即可：

WITH
cte
AS  (
    SELECT
        c.LookupCode
        , cn.LkPrefix 'Title'
        , cn.FirstName
        , cn.MiddleName
        , cn.LastName
        , CAST(cn2.Number AS VARCHAR(150)) 'Number'
        , CAST(cn2.EmailWeb AS VARCHAR(150)) 'EmailWeb'
        , cn2.TypeCode
        , cn2.UniqContactName
        , IIF(c.UniqContactNamePrimary = cn2.UniqContactName, 1, 0) "Primary"
    FROM
        dbo.Client        c
    LEFT OUTER JOIN
        dbo.ContactNumber cn2           
            ON cn2.UniqEntity = c.UniqEntity
    LEFT OUTER JOIN dbo.ContactName cn ON c.UniqEntity = cn.UniqEntity AND cn2.UniqContactName = cn.UniqContactName
    WHERE
        c.LookupCode = @LookupCode
)
SELECT
    LookupCode,
    Title,
    FirstName,
    MiddleName,
    LastName,
    COALESCE(MAX(CASE WHEN TypeCode = 'RES' AND [Primary] =1 THEN Number END), MAX(CASE WHEN TypeCode = 'RES' THEN Number END)) AS Home,
    COALESCE(MAX(CASE WHEN TypeCode = 'MOB' AND [Primary] =1 THEN Number END), MAX(CASE WHEN TypeCode = 'MOB' THEN Number END)) AS Mobile,
    COALESCE(MAX(CASE WHEN TypeCode = 'EM1' AND [Primary] =1 THEN EmailWeb END), MAX(CASE WHEN TypeCode = 'EM1' THEN EmailWeb END)) AS Email
FROM cte
-- 按联系人唯一标识+基础属性分组，确保每个联系人仅返回一行
GROUP BY LookupCode, Title, FirstName, MiddleName, LastName, UniqContactName

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