如何在SQL Server中按Team分组将成员多行数据合并为单行？

我有一张存储团队成员信息的表，结构及数据如下：

ID  Team Name    Role
1   1    Adam    Lead
2   1    Barbara Analyst
3   1    Connor  Logistics
4   2    David   Lead
5   2    Ethan   Logistics
6   3    Faith   Lead
7   3    George  Analyst
8   3    Hannah  Analyst

需要按Team字段分组，将每个团队的成员数据合并为单行，每个团队最多显示3名成员，格式如下：

Team 1st Member Name 1st Member Role 2nd Member Name 2nd Member Role 3rd Member Name 3rd Member Role
1    Adam            Lead            Barbara         Analyst         Connor          Logistics
2    David           Lead            Ethan           Logistics       NULL            NULL
3    Faith           Lead            George          Analyst         Hannah          Analyst

尝试过PIVOT表和动态SQL，但没能实现该逻辑，请问如何在SQL Server中完成？

解决方案 #

可以通过窗口函数生成成员序号 + 条件聚合的方式实现，具体步骤如下：

1. 为每个团队内的成员分配序号 #

使用ROW_NUMBER()窗口函数，按团队分组，为每个成员分配组内序号（这里按ID排序保证顺序和原始数据一致，可根据实际需求调整排序字段）。

2. 通过条件聚合转成单行多列 #

基于生成的序号，使用MAX(CASE ...)的条件聚合语法，分别提取每个序号对应的成员姓名和角色，最终按团队分组得到目标格式。

完整SQL代码如下：

WITH TeamMembersWithRank AS (
    SELECT 
        Team,
        Name,
        Role,
        -- 按Team分组，给每个成员分配组内序号
        ROW_NUMBER() OVER (PARTITION BY Team ORDER BY ID) AS MemberRank
    FROM YourTableName -- 替换为你的实际表名
)
SELECT
    Team,
    -- 提取第1个成员的姓名和角色
    MAX(CASE WHEN MemberRank = 1 THEN Name END) AS [1st Member Name],
    MAX(CASE WHEN MemberRank = 1 THEN Role END) AS [1st Member Role],
    -- 提取第2个成员的姓名和角色
    MAX(CASE WHEN MemberRank = 2 THEN Name END) AS [2nd Member Name],
    MAX(CASE WHEN MemberRank = 2 THEN Role END) AS [2nd Member Role],
    -- 提取第3个成员的姓名和角色
    MAX(CASE WHEN MemberRank = 3 THEN Name END) AS [3rd Member Name],
    MAX(CASE WHEN MemberRank = 3 THEN Role END) AS [3rd Member Role]
FROM TeamMembersWithRank
GROUP BY Team
ORDER BY Team;

说明 #

• ROW_NUMBER()确保每个团队内的成员有唯一序号，超过3的成员会被自动忽略，符合需求。
• 条件聚合MAX(CASE ...)会提取对应序号的字段值，无对应成员时返回NULL，匹配目标格式的空值要求。
• 该方案无需动态SQL，结构固定，适合“每个团队最多3名成员”的固定列场景，也能适配包含更多行和重复字段的实际数据集。

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