如何将UNION查询返回的两行结果合并为单行（SQL实现）

问题描述 #

现有如下SQL查询语句，通过UNION合并两个查询逻辑的结果，当前返回两行数据。需要将结果合并为单行，同时包含原始呼叫信息及转接后的代理、技能组、脚本等字段，尝试相关方案未成功，需实现符合预期结果的SQL改写。

原查询语句 #

DECLARE @dateFrom DATETIME 
SELECT @dateFrom = CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME) 
SELECT TCD.DigitsDialed   AS [OriginalDigitsDialed], 
  A.EnterpriseName   AS [OriginalAgentEnterpriseName], 
  SG.EnterpriseName  AS [OriginalAgentSkillGroup],
    MS.EnterpriseName AS [OriginalScript],
  RCD.BeganRoutingDateTime [TransferDateTime],  
  RCD.DialedNumberString AS [TransferDestinationVDN]
FROM dbo.Termination_Call_Detail TCD 
  LEFT OUTER JOIN (SELECT * 
      FROM Route_Call_Detail 
      WHERE DateTime > @dateFrom) RCD 
     ON TCD.RouterCallKeyDay = RCD.RouterCallKeyDay 
      AND TCD.RouterCallKey = RCD.RouterCallKey 
      AND RCD.RouterCallKeySequenceNumber < TCD.RouterCallKeySequenceNumber 
  LEFT OUTER JOIN Agent A 
     ON A.SkillTargetID = TCD.AgentSkillTargetID 
  LEFT OUTER JOIN Skill_Group SG 
     ON TCD.SkillGroupSkillTargetID = SG.SkillTargetID 
    LEFT OUTER JOIN Script S
         ON RCD.ScriptID = S.ScriptID
    LEFT OUTER JOIN Master_Script MS
         ON S.MasterScriptID = MS.MasterScriptID
WHERE TCD.DateTime > @dateFrom 
    AND RCD.RequestType = '2'
  AND TCD.AgentSkillTargetID IS NOT NULL
  AND RCD.RouterCallKeyDay = '154514'
  AND RCD.RouterCallKey = '2556'
UNION
SELECT TCD.DigitsDialed   AS [OriginalDigitsDialed], 
  A.EnterpriseName   AS [OriginalAgentEnterpriseName], 
  SG.EnterpriseName  AS [OriginalAgentSkillGroup],
    MS.EnterpriseName AS [OriginalScript],
  RCD.BeganRoutingDateTime [TransferDateTime],  
  RCD.DialedNumberString AS [TransferDestinationVDN]
FROM dbo.Termination_Call_Detail TCD 
  LEFT OUTER JOIN (SELECT * 
      FROM Route_Call_Detail 
      WHERE DateTime > @dateFrom) RCD 
     ON TCD.RouterCallKeyDay = RCD.RouterCallKeyDay 
      AND TCD.RouterCallKey = RCD.RouterCallKey 
      AND RCD.RouterCallKeySequenceNumber < TCD.RouterCallKeySequenceNumber 
  LEFT OUTER JOIN Agent A 
     ON A.SkillTargetID = TCD.AgentSkillTargetID 
  LEFT OUTER JOIN Skill_Group SG 
     ON TCD.SkillGroupSkillTargetID = SG.SkillTargetID 
    LEFT OUTER JOIN Script S
         ON RCD.ScriptID = S.ScriptID
    LEFT OUTER JOIN Master_Script MS
         ON S.MasterScriptID = MS.MasterScriptID
WHERE TCD.DateTime > @dateFrom 
  AND TCD.CallDisposition IN ('28','29') 
    AND RCD.RequestType = '6'
  AND TCD.AgentSkillTargetID IS NOT NULL
  AND RCD.RouterCallKeyDay = '154514'
  AND RCD.RouterCallKey = '2556'

期望结果 #

解决方案 #

原查询用UNION合并行数据，而我们需要合并列，因此将两个查询作为子查询，通过共同的呼叫标识（RouterCallKeyDay、RouterCallKey）关联，实现单行输出：

DECLARE @dateFrom DATETIME 
SELECT @dateFrom = CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME) 

-- 原始呼叫信息子查询
SELECT 
  Original.OriginalDigitsDialed,
  Original.OriginalAgentEnterpriseName,
  Original.OriginalAgentSkillGroup,
  Original.OriginalScript,
  Original.TransferDateTime,
  Original.TransferDestinationVDN,
  Xfer.XferAgentEnterpriseName,
  Xfer.XferAgentSkillGroup,
  Xfer.XferScript
FROM (
  SELECT 
    TCD.DigitsDialed AS [OriginalDigitsDialed], 
    A.EnterpriseName AS [OriginalAgentEnterpriseName], 
    SG.EnterpriseName AS [OriginalAgentSkillGroup],
    MS.EnterpriseName AS [OriginalScript],
    RCD.BeganRoutingDateTime AS [TransferDateTime],  
    RCD.DialedNumberString AS [TransferDestinationVDN],
    RCD.RouterCallKeyDay,
    RCD.RouterCallKey
  FROM dbo.Termination_Call_Detail TCD 
    LEFT OUTER JOIN (SELECT * FROM Route_Call_Detail WHERE DateTime > @dateFrom) RCD 
      ON TCD.RouterCallKeyDay = RCD.RouterCallKeyDay 
      AND TCD.RouterCallKey = RCD.RouterCallKey 
      AND RCD.RouterCallKeySequenceNumber < TCD.RouterCallKeySequenceNumber 
    LEFT OUTER JOIN Agent A ON A.SkillTargetID = TCD.AgentSkillTargetID 
    LEFT OUTER JOIN Skill_Group SG ON TCD.SkillGroupSkillTargetID = SG.SkillTargetID 
    LEFT OUTER JOIN Script S ON RCD.ScriptID = S.ScriptID
    LEFT OUTER JOIN Master_Script MS ON S.MasterScriptID = MS.MasterScriptID
  WHERE TCD.DateTime > @dateFrom 
    AND RCD.RequestType = '2'
    AND TCD.AgentSkillTargetID IS NOT NULL
    AND RCD.RouterCallKeyDay = '154514'
    AND RCD.RouterCallKey = '2556'
) AS Original
-- 关联转接后的信息子查询
INNER JOIN (
  SELECT 
    A.EnterpriseName AS [XferAgentEnterpriseName], 
    SG.EnterpriseName AS [XferAgentSkillGroup],
    MS.EnterpriseName AS [XferScript],
    RCD.RouterCallKeyDay,
    RCD.RouterCallKey
  FROM dbo.Termination_Call_Detail TCD 
    LEFT OUTER JOIN (SELECT * FROM Route_Call_Detail WHERE DateTime > @dateFrom) RCD 
      ON TCD.RouterCallKeyDay = RCD.RouterCallKeyDay 
      AND TCD.RouterCallKey = RCD.RouterCallKey 
      AND RCD.RouterCallKeySequenceNumber < TCD.RouterCallKeySequenceNumber 
    LEFT OUTER JOIN Agent A ON A.SkillTargetID = TCD.AgentSkillTargetID 
    LEFT OUTER JOIN Skill_Group SG ON TCD.SkillGroupSkillTargetID = SG.SkillTargetID 
    LEFT OUTER JOIN Script S ON RCD.ScriptID = S.ScriptID
    LEFT OUTER JOIN Master_Script MS ON S.MasterScriptID = MS.MasterScriptID
  WHERE TCD.DateTime > @dateFrom 
    AND TCD.CallDisposition IN ('28','29') 
    AND RCD.RequestType = '6'
    AND TCD.AgentSkillTargetID IS NOT NULL
    AND RCD.RouterCallKeyDay = '154514'
    AND RCD.RouterCallKey = '2556'
) AS Xfer
ON Original.RouterCallKeyDay = Xfer.RouterCallKeyDay 
AND Original.RouterCallKey = Xfer.RouterCallKey

关键说明 #

• 将两个原查询分别封装为Original（原始呼叫）和Xfer（转接后）子查询，保留共同的呼叫标识字段用于关联
• 使用INNER JOIN关联两个子查询，确保同一呼叫的原始与转接信息合并到单行
• 按需选择两个子查询中的字段，组合成期望的输出结构

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