时间戳

首页 > 解决方案 > 如何将两条记录与附加列合并为一条

sql - 如何将两条记录与附加列合并为一条

问题描述

当我运行下面的查询时

SELECT 
 ML.MemberID
,ML.effective_date
,CL.CaseID
,CL.CaseName
,CL.StartDate AS CaseCreatedDate    
,CONCAT(U.firstname,' ', U.lastname) AS PrimaryCaseOwner
,CONCAT(U2.firstname,' ', U2.lastname) AS SecondaryCaseOwner
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 Cases_SecondaryOwners S ON CL.case_id = S.case_id
    LEFT JOIN Users U2 ON S.[user_id] = U2.[user_id] 
    where MemberID in ( 16468)  
ORDER BY MemberName, CaseCreatedDate

它返回这个：

MemberID  :  Effective_Date : CaseID : CaseName : CaseDate : CaseOwner : 2nd Owner
45555          6/2/2019        0002     General   8/11/2019    John A.
45555          7/31/2019       0003     Special   9/1/2019     Jimmy       Tyler
45555          7/31/2019       0003     Special   9/1/2019     Jimmy       John A.

但我希望得到这样的结果：

MemberID  :  Effective_Date : CaseID : CaseName : CaseDate : CaseOwner : 2nd Owner1 : 2nd Owner2
45555          6/2/2019        0002     General   8/11/2019    John A.
45555          7/31/2019       0003     Special   9/1/2019     Jimmy       Tyler       John A.

我怎样才能做到这一点？谢谢！

标签： sqlsql-servertsql

解决方案

使用条件聚合：

SELECT 
     ML.MemberID
    ,ML.effective_date
    ,CL.CaseID
    ,CL.CaseName
    ,CL.StartDate AS CaseCreatedDate    
    ,CONCAT(U.firstname,' ', U.lastname) AS PrimaryCaseOwner
    MIN(CONCAT(U2.firstname,' ', U2.lastname)) AS SecondaryCaseOwner1,
    CASE 
        WHEN MIN(U2.[user_id]) != MAX(U2.[user_id])
        THEN MAX(CONCAT(U2.firstname,' ', U2.lastname)) 
    END AS SecondaryCaseOwner2
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 Cases_SecondaryOwners S ON CL.case_id = S.case_id
    LEFT JOIN Users U2 ON S.[user_id] = U2.[user_id] 
WHERE MemberID in = 16468
GROUP BY
     ML.MemberID
    ,ML.effective_date
    ,CL.CaseID
    ,CL.CaseName
    ,CL.StartDate AS CaseCreatedDate    
    ,U.firstname
    ,U.lastname
ORDER BY MemberName, CaseCreatedDate

笔记：

  • 此查询仅对不超过 2 个二级用户有意义；否则，您将获得按全名字母顺序排序的第一个和最后一个用户名

  • 我将WHERE条款中的条件从更改MemberID in IN (16468)为MemberID in = 16468; IN当列表中有多个元素时使用更好。

推荐阅读