按Issue和Client合并Label列多行值的Pandas及SQL实现方案咨询

Pandas 实现 #

你之前的代码仅指定了Label列的聚合逻辑，因此返回结果只有三列。要保留其余列，只需为剩余非分组列统一指定聚合规则为first（取分组内第一行值）即可。
如果列数较少可直接手动指定聚合规则：

result_df = df.groupby(
    ['Issue', 'Client'],
    as_index=False,
    sort=False # 保留原始数据的行顺序
).agg({
    'Label': ','.join,
    'Subject': 'first',
    'type': 'first',
    'Team': 'first',
    'Subteam': 'first',
    'Priority': 'first',
    'CreatedOn': 'first',
    'BuiltOn': 'first',
    'CreatedBy': 'first',
    'Status': 'first'
})

如果列数较多不想逐个书写，可通过字典推导式批量生成聚合规则：

# 定义需要特殊处理的列
special_agg = {'Label': ','.join}
# 剩余非分组列统一取分组首行值
normal_agg = {
    col: 'first' 
    for col in df.columns 
    if col not in ['Issue', 'Client', 'Label']
}
# 合并聚合规则
full_agg = {**normal_agg, **special_agg}

result_df = df.groupby(
    ['Issue', 'Client'],
    as_index=False,
    sort=False
).agg(full_agg)

SQL 实现（适配SQL Server 2014） #

SQL Server 2014没有内置STRING_AGG拼接函数，需要使用FOR XML PATH语法实现字符串拼接，同时配合窗口函数ROW_NUMBER取分组内首行的其余字段值：

WITH grouped_base AS (
    -- 为每个Issue+Client分组的行编号，取编号=1的行作为其余字段的来源
    SELECT 
        I.Issue,
        I.Subject,
        I.type, 
        P.Team, 
        P.Subteam,
        CR.Client,
        I.Priority,
        I.CreatedOn,
        I.BuiltOn,
        I.CreatedBy,
        I.Status,
        ROW_NUMBER() OVER (
            PARTITION BY I.Issue, CR.Client 
            ORDER BY (SELECT 0) -- 按默认顺序取首行，有排序需求可替换为实际字段比如I.CreatedOn
        ) AS row_num
    FROM master.IssueRequests AS I 
    JOIN master.Participants AS P 
      ON P.Issue = I.Issue 
    JOIN master.ClientRecords AS CR 
      ON CR.Issue = I.Issue
    WHERE I.Issue IN ('2652523', '2703670', '2984120')
),
label_merge AS (
    -- 拼接每个分组的所有Label值
    SELECT 
        L.Issue,
        CR.Client,
        STUFF((
            SELECT ',' + L2.Label
            FROM master.IssueLabels AS L2
            WHERE L2.Issue = L.Issue
            FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS Label
    FROM master.IssueLabels AS L
    JOIN master.ClientRecords AS CR ON CR.Issue = L.Issue
    WHERE L.Issue IN ('2652523', '2703670', '2984120')
    GROUP BY L.Issue, CR.Client
)
-- 关联两个结果集得到最终数据
SELECT 
    g.Issue,
    g.Subject,
    g.type,
    g.Team,
    g.Subteam,
    g.Client,
    g.Priority,
    g.CreatedOn,
    l.Label,
    g.BuiltOn,
    g.CreatedBy,
    g.Status
FROM grouped_base g
INNER JOIN label_merge l 
  ON g.Issue = l.Issue AND g.Client = l.Client
WHERE g.row_num = 1

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