如何合并两条SQL记录为一条并新增列？查询结果合并需求

你现在碰到的问题是因为一个案件对应多个二级负责人，直接左连Cases_SecondaryOwners表会把每个负责人拆成单独的行，而你想要把这些负责人合并到同一行的不同列里。下面给你两种实用的解决方法：

方法1：使用CTE+多左连接实现行转列 #

这种方法先给每个案件的二级负责人分配一个序号，再通过多次左连接把不同序号的负责人映射到单独的列：

WITH SecondaryOwnersWithRank AS (
    SELECT 
        CL.CaseID,
        CONCAT(U2.firstname,' ', U2.lastname) AS SecondaryOwnerName,
        -- 按姓氏排序给每个CaseID下的二级负责人分配序号
        ROW_NUMBER() OVER (PARTITION BY CL.CaseID ORDER BY U2.lastname) AS OwnerRank
    FROM 
        vw_CaseList CL
    LEFT JOIN 
        Cases_SecondaryOwners S ON CL.case_id = S.case_id
    LEFT JOIN 
        Users U2 ON S.[user_id] = U2.[user_id]
    WHERE 
        CL.StartDate < GETDATE() AND (CL.EndDate IS NULL OR CL.EndDate > GETDATE())
)
SELECT 
    ML.MemberID,
    ML.effective_date,
    CL.CaseID,
    CL.CaseName,
    CL.StartDate AS CaseCreatedDate,
    CONCAT(U.firstname,' ', U.lastname) AS PrimaryCaseOwner,
    -- 提取第1个二级负责人
    SO1.SecondaryOwnerName AS [2nd Owner1],
    -- 提取第2个二级负责人
    SO2.SecondaryOwnerName AS [2nd Owner2]
FROM 
    #MemberList ML
INNER JOIN 
    vw_CaseList CL ON ML.member_id = CL.member_id 
                   AND CL.StartDate < GETDATE() 
                   AND (CL.EndDate IS NULL OR CL.EndDate > GETDATE())
INNER JOIN 
    Users U ON CL.primary_owner_id = U.[user_id]
LEFT JOIN 
    SecondaryOwnersWithRank SO1 ON CL.CaseID = SO1.CaseID AND SO1.OwnerRank = 1
LEFT JOIN 
    SecondaryOwnersWithRank SO2 ON CL.CaseID = SO2.CaseID AND SO2.OwnerRank = 2
WHERE 
    ML.MemberID IN (16468)
ORDER BY 
    ML.MemberName, CL.StartDate;

方法2：使用PIVOT（数据透视）实现行转列 #

如果你的SQL Server版本支持PIVOT（2005及以上），可以用这种更简洁的方式：

WITH SecondaryOwnersWithRank AS (
    SELECT 
        CL.CaseID,
        CONCAT(U2.firstname,' ', U2.lastname) AS SecondaryOwnerName,
        ROW_NUMBER() OVER (PARTITION BY CL.CaseID ORDER BY U2.lastname) AS OwnerRank
    FROM 
        vw_CaseList CL
    LEFT JOIN 
        Cases_SecondaryOwners S ON CL.case_id = S.case_id
    LEFT JOIN 
        Users U2 ON S.[user_id] = U2.[user_id]
    WHERE 
        CL.StartDate < GETDATE() AND (CL.EndDate IS NULL OR CL.EndDate > GETDATE())
),
PivotedOwners AS (
    SELECT 
        CaseID,
        [1] AS [2nd Owner1],
        [2] AS [2nd Owner2]
    FROM 
        SecondaryOwnersWithRank
    PIVOT (
        MAX(SecondaryOwnerName)
        FOR OwnerRank IN ([1], [2])
    ) AS PivotResult
)
SELECT 
    ML.MemberID,
    ML.effective_date,
    CL.CaseID,
    CL.CaseName,
    CL.StartDate AS CaseCreatedDate,
    CONCAT(U.firstname,' ', U.lastname) AS PrimaryCaseOwner,
    PO.[2nd Owner1],
    PO.[2nd Owner2]
FROM 
    #MemberList ML
INNER JOIN 
    vw_CaseList CL ON ML.member_id = CL.member_id 
                   AND CL.StartDate < GETDATE() 
                   AND (CL.EndDate IS NULL OR CL.EndDate > GETDATE())
INNER JOIN 
    Users U ON CL.primary_owner_id = U.[user_id]
LEFT JOIN 
    PivotedOwners PO ON CL.CaseID = PO.CaseID
WHERE 
    ML.MemberID IN (16468)
ORDER BY 
    ML.MemberName, CL.StartDate;

关键逻辑说明 #

1. 首先用ROW_NUMBER() OVER (PARTITION BY CL.CaseID ...)给每个案件下的二级负责人分配唯一序号，这样同一个案件的不同负责人会被标记为1、2、3...
2. 然后通过多左连接或者PIVOT的方式，把不同序号的负责人映射到对应的2nd Owner1、2nd Owner2列中
3. 这样就避免了同一个案件因为多个二级负责人而拆分出多行的问题，最终结果会和你期望的一致

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