sql语句合并重复数据 将指定字段值用逗号分割

Question

sqlserver如何将此条sql查询结果合并成一条 将三条数据的manifestId字段用逗号分割 sqlserver版本为2012

SELECT
    t.*FROM((
    SELECT
    d.AwbPrefix,d.AwbNo ,d.AwbPostfix,NULL hwbNo,d.Manifest_ID manifestId,
        b.Routing,b.AgentCode,b.Goods goodsName,h.HsCode,h.HsNameCN,h.HsNameEN,h.Piece,h.Weight,h.PackageName,
    h.DeclaredValueForCustoms,h.CurrencyName,h.IsAgree,h.DataSource,
    ISNULL(l.AgentCode, NULL) as white,h.HouseId
    FROM
    TBL_DepManifest d with(nolock)
    LEFT JOIN TBL_AWB_BASIC b with(nolock) ON d.AwbNo = b.AwbNo
    LEFT JOIN Tbl_Awb_HsDeclare h with(nolock) ON d.AwbNo = h.AwbNo and h.HwbNo IS NULL
    left join Tbl_HsCodeWhiteListAgent l with(nolock) on b.AgentCode=l.AgentCode
    ) UNION ALL
    (
    SELECT
    d.AwbPrefix,d.AwbNo,d.AwbPostfix,ha.HwbNo hwbNo,d.Manifest_ID manifestId,b.Routing,b.AgentCode,
    b.Goods goodsName,h.HsCode,h.HsNameCN,h.HsNameEN,h.Piece, h.Weight,h.PackageName,h.DeclaredValueForCustoms,
    h.CurrencyName,h.IsAgree,h.DataSource,ISNULL(l.AgentCode, NULL) as white,h.HouseId
    FROM
    TBL_AWB_HAWB ha with(nolock)
    LEFT JOIN TBL_AWB_BASIC b with(nolock) ON ha.AwbNo = b.AwbNo
    LEFT JOIN Tbl_Awb_HsDeclare h with(nolock) ON ha.HwbNo = h.HwbNo
    LEFT JOIN TBL_DepManifest d  with(nolock) ON d.AwbPrefix=ha.AwbPrefix and d.AwbNo = ha.AwbNo
    AND d.AwbPostfix = ha.AwbPostfix
    left join Tbl_HsCodeWhiteListAgent l with(nolock) on b.AgentCode=l.AgentCode
    )
    )t
        where t.AwbNo='55907865'
                order by t.AwbNo,t.hwbNo asc
    offset #{dto.pageNum} * #{dto.pageSize} ROWS FETCH NEXT #{dto.pageSize} ROWS ONLY

查询结果

Answer 1

引用new bing部分指引作答：
要将查询结果中的manifestId字段合并成一条，可以使用SQL Server中的STUFF和FOR XML PATH函数。具体步骤如下：

1 在SELECT语句中，将manifestId字段替换为以下内容：

STUFF((SELECT ',' + CAST(manifestId AS VARCHAR(100))
FROM TBL_DepManifest d2 with(nolock)
WHERE d2.AwbNo = d.AwbNo
AND d2.Manifest_ID = d.Manifest_ID
FOR XML PATH('')), 1, 1, '') AS manifestId

这个子查询会将相同AwbNo和Manifest_ID的记录的manifestId字段用逗号连接起来，作为一个新的manifestId字段返回。

2 将整个SELECT语句包裹在一个外部SELECT语句中，使用DISTINCT关键字去重，例如：

SELECT DISTINCT t.AwbPrefix, t.AwbNo, t.AwbPostfix, t.hwbNo,
STUFF((SELECT ',' + CAST(manifestId AS VARCHAR(100))
FROM TBL_DepManifest d2 with(nolock)
WHERE d2.AwbNo = d.AwbNo
AND d2.Manifest_ID = d.Manifest_ID
FOR XML PATH('')), 1, 1, '') AS manifestId,
t.Routing, t.AgentCode, t.goodsName, t.HsCode,
t.HsNameCN, t.HsNameEN, t.Piece, t.Weight, t.PackageName,
t.DeclaredValueForCustoms, t.CurrencyName, t.IsAgree, t.DataSource,
t.white, t.HouseId
FROM (
-- 原始查询语句
) t
WHERE t.AwbNo='55907865'
ORDER BY t.AwbNo, t.hwbNo ASC
OFFSET #{dto.pageNum} * #{dto.pageSize} ROWS FETCH NEXT #{dto.pageSize} ROWS ONLY

这个外部SELECT语句会保留每个不同的AwbNo和manifestId组合的唯一记录，并将它们的manifestId字段用逗号连接起来。