新增文章

文章标题分类内容

# MSSQL 以多个字段分组并将某个字段的不同值合并成一串字符（逗号分隔）
 
   select *

,isnull( stuff((

select ',' + FCreator

from ( select distinct FBillNo_Rpt,FSeq_Rpt,FBillNo_PpBom,FMtrlNo2,FUnitName2,FScrapRate,FDenominator  ,FCreator   from  TMP69E1ED8B1F7811F0ACC00050563_1 where FCreator != '' ) t2

where t1.FBillNo_Rpt   = t2.FBillNo_Rpt   and t1.FSeq_Rpt   = t2.FSeq_Rpt 
          and t1.FBillNo_PpBom = t2.FBillNo_PpBom 
          and t1.FMtrlNo2      = t2.FMtrlNo2      and t1.FUnitName2 = t2.FUnitName2 
          and t1.FScrapRate    = t2.FScrapRate 
          and t1.FDenominator  = t2.FDenominator

for xml path('')

), 1, 1, '')  ,'') as FCreatorTxt

from TMP69E1ED8B1F7811F0ACC00050563  t1

在MSSQL中，你可以使用STRING_AGG函数（SQL Server 2017及更高版本）或FOR XML PATH方法（旧版本）来实现按多个字段分组并将某个字段的不同值合并成逗号分隔的字符串。

## 方法1：使用STRING_AGG函数（SQL Server 2017+）

```sql
SELECT 
    field1, 
    field2, 
    field3,
    STRING_AGG(DISTINCT value_field, ',') AS concatenated_values
FROM 
    your_table
GROUP BY 
    field1, field2, field3;
```

## 方法2：使用FOR XML PATH方法（SQL Server 2005+）

```sql
SELECT 
    field1, 
    field2, 
    field3,
    STUFF((
        SELECT ',' + value_field
        FROM your_table t2
        WHERE t1.field1 = t2.field1
          AND t1.field2 = t2.field2
          AND t1.field3 = t2.field3
        FOR XML PATH('')
    ), 1, 1, '') AS concatenated_values
FROM 
    your_table t1
GROUP BY 
    field1, field2, field3;
```

## 示例

假设有一个订单明细表，你想按客户ID和订单日期分组，合并所有产品名称：

```sql
-- 使用STRING_AGG
SELECT 
    CustomerID,
    CONVERT(date, OrderDate) AS OrderDate,
    STRING_AGG(DISTINCT ProductName, ', ') AS Products
FROM 
    OrderDetails
GROUP BY 
    CustomerID, CONVERT(date, OrderDate);

-- 使用FOR XML PATH
SELECT 
    CustomerID,
    CONVERT(date, OrderDate) AS OrderDate,
    STUFF((
        SELECT ', ' + ProductName
        FROM OrderDetails t2
        WHERE t1.CustomerID = t2.CustomerID
          AND CONVERT(date, t1.OrderDate) = CONVERT(date, t2.OrderDate)
        FOR XML PATH('')
    ), 1, 2, '') AS Products
FROM 
    OrderDetails t1
GROUP BY 
    CustomerID, CONVERT(date, OrderDate);
```

注意：
1. STRING_AGG是更现代、更简洁的方法
2. 如果需要去除重复值，STRING_AGG可以直接使用DISTINCT关键字
3. FOR XML PATH方法需要手动处理第一个逗号（使用STUFF函数）
4. 两种方法都可以添加排序：STRING_AGG使用WITHIN GROUP，FOR XML PATH在子查询中添加ORDER BY