SQL Server 2022：如何将用户关联物品集合的名称聚合为单个字符串？

表结构与数据 #

Person表 #

Person
------
PersonId int PK
Name varchar
ThingCollectionId int

=======================
PersonId    Name    ThingCollectionId
1           Joe     100
2           Mary    101
3           Dave    102

Thing表 #

Thing
-----
ThingId int PK
ThingCollectionId int
Name varchar

=======================
ThingId ThingCollectionId   Name
1       101                 Baseball
2       102                 Basketball
3       102                 Football

需求说明 #

Thing表中每行代表一个物品，物品通过ThingCollectionId分组为集合；每个Person关联一个物品集合。需编写单条查询语句，将Person表每行对应的物品集合中所有物品名称聚合为逗号分隔的字符串，集合物品数量可为0或任意正整数，期望查询结果如下：

PersonId    Name    Things
1           Joe     [NULL]
2           Mary    Baseball
3           Dave    Basketball,Football

使用环境：SQL Server 2022版本

Microsoft SQL Server 2022 (RTM-CU13) (KB5036432) - 16.0.4125.3 (X64) 
    May  1 2024 15:05:56 
    Copyright (C) 2022 Microsoft Corporation
    Developer Edition (64-bit) on Linux (Ubuntu 22.04.4 LTS) <X64>

解决方案 #

可以通过单条查询实现，利用SQL Server的STRING_AGG聚合函数结合左连接即可满足需求：

SELECT 
    p.PersonId,
    p.Name,
    STRING_AGG(t.Name, ',') AS Things
FROM Person p
LEFT JOIN Thing t 
    ON p.ThingCollectionId = t.ThingCollectionId
GROUP BY p.PersonId, p.Name;

逻辑说明 #

• LEFT JOIN保证所有Person记录都被保留，即便对应的物品集合没有任何物品
• STRING_AGG会自动将同一ThingCollectionId下的物品名称用逗号拼接；当无匹配物品时，该字段返回NULL，与期望结果一致

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