sqlserver拼接分组字符串

最新推荐文章于 2025-11-11 09:13:34 发布

原创最新推荐文章于 2025-11-11 09:13:34 发布 · 161 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sqlserver #数据库

sqlserver 专栏收录该内容

4 篇文章

订阅专栏

--1. STRING_AGG函数（SQL Server 2017及以上推荐）
--sql
-- 基本用法
SELECT 
    department_id,
    STRING_AGG(employee_name, ', ') AS employees
FROM employees
GROUP BY department_id;

-- 带排序的拼接
SELECT 
    department_id,
    STRING_AGG(employee_name, ', ') WITHIN GROUP (ORDER BY employee_name) AS employees
FROM employees
GROUP BY department_id;


--2. FOR XML PATH方法（兼容旧版本）
--sql
SELECT 
    department_id,
    STUFF((
        SELECT ', ' + employee_name
        FROM employees e2
        WHERE e2.department_id = e1.department_id
        FOR XML PATH('')
    ), 1, 2, '') AS employees
FROM employees e1
GROUP BY department_id;

-- 带排序的版本
SELECT 
    department_id,
    STUFF((
        SELECT ', ' + employee_name
        FROM employees e2
        WHERE e2.department_id = e1.department_id
        ORDER BY employee_name
        FOR XML PATH('')
    ), 1, 2, '') AS employees
FROM employees e1
GROUP BY department_id;