合并重复行并基于单列生成多列，实现跨源账户代码一致性识别

我需要合并不同来源的账户列表，识别哪些账户代码在各来源中的设置是一致的。

SELECT
    Distinct a.MainCode,
    a.Type,
    a.Group,
    a.InternalCode
FROM 700_AccountTable a
Left join 700_AccountDetail b
    on a.MainCode = b.MainCode
Left join 700_AccountHistory c
    on a.AccountKey = c.AccountKey
Where 
    b.Status = 'Active'
GROUP BY
    a.MainCode,
    a.Type,
    a.Group,
    a.InternalCode
HAVING
    count(c.AccountKey) > 0


UNION


SELECT
    Distinct a.MainCode,
    a.Type,
    a.Group,
    a.InternalCode
FROM 111_AccountTable a
Left join 111_AccountDetail b
    on a.MainCode = b.MainCode
Left join 111_AccountHistory c
    on a.AccountKey = c.AccountKey
Where 
    b.Status = 'Active'
GROUP BY
    a.MainCode,
    a.Type,
    a.Group,
    a.InternalCode
HAVING
    count(c.AccountKey) > 0

通过条件聚合将行数据转为列，直接展示每个账户在不同来源的存在情况，修改后的SQL如下：

WITH CombinedAccounts AS (
    SELECT
        a.MainCode,
        a.Type,
        a.Group,
        a.InternalCode
    FROM 700_AccountTable a
    LEFT JOIN 700_AccountDetail b ON a.MainCode = b.MainCode
    LEFT JOIN 700_AccountHistory c ON a.AccountKey = c.AccountKey
    WHERE b.Status = 'Active'
    GROUP BY a.MainCode, a.Type, a.Group, a.InternalCode
    HAVING COUNT(c.AccountKey) > 0
    
    UNION ALL
    
    SELECT
        a.MainCode,
        a.Type,
        a.Group,
        a.InternalCode
    FROM 111_AccountTable a
    LEFT JOIN 111_AccountDetail b ON a.MainCode = b.MainCode
    LEFT JOIN 111_AccountHistory c ON a.AccountKey = c.AccountKey
    WHERE b.Status = 'Active'
    GROUP BY a.MainCode, a.Type, a.Group, a.InternalCode
    HAVING COUNT(c.AccountKey) > 0
)
SELECT
    MainCode,
    Type,
    Group,
    CASE WHEN MAX(CASE WHEN InternalCode = '700' THEN 'X' END) IS NOT NULL THEN 'X' ELSE '' END AS [700],
    CASE WHEN MAX(CASE WHEN InternalCode = '111' THEN 'X' END) IS NOT NULL THEN 'X' ELSE '' END AS [111]
FROM CombinedAccounts
GROUP BY MainCode, Type, Group
ORDER BY MainCode, Type;

说明 #

1. 用WITH子句整合两个来源的有效账户数据，使用UNION ALL避免去重导致的信息丢失
2. 外层通过嵌套CASE语句做聚合判断：若账户在对应来源存在则标记为X，否则留空
3. 按MainCode、Type、Group分组，确保每个唯一组合只显示一行

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