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

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

查询结果

引用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字段用逗号连接起来。

要将SQL查询的结果组合成一个包含三个以逗号分隔的数据的单个manifest，可以使用SQL Server中的STRING_AGG函数。下面是一个修改SQL查询以达到预期结果的例子:

SELECT
    t.AwbPrefix, t.AwbNo, t.AwbPostfix, 
    STRING_AGG(t.manifestId, ',') AS CombinedManifestId,
    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, ISNULL(t.white, NULL) AS white, t.HouseId
FROM
(
    (
    SELECT
        d.AwbPrefix, d.AwbNo, d.AwbPostfix, NULL AS hwbNo, d.Manifest_ID AS manifestId,
        b.Routing, b.AgentCode, b.Goods AS 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 AS hwbNo, d.Manifest_ID AS manifestId,
        b.Routing, b.AgentCode, b.Goods AS 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
    )
) AS 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子句中添加了STRING_AGG函数，指定manifold列作为要聚合的表达式，并用它作为分隔符。我们还为聚合列添加了combinedmanifold别名。最后，我们在中包含了这个别名

这篇博客: SQL数据库语言基础之SqlServer视图的创建、修改与视图数据的增删改查中的 文章目录 部分也许能够解决你的问题, 你可以仔细阅读以下内容或跳转源博客中阅读:

该回答通过自己思路及引用到GPTᴼᴾᴱᴺᴬᴵ搜索,得到内容具体如下：
要将查询结果合并成一条记录，可以使用STUFF和FOR XML PATH函数来将指定字段值用逗号分隔。具体操作如下：

1、在SELECT语句中，将需要合并的字段用STUFF和FOR XML PATH函数拼接成一个逗号分隔的字符串，例如：

SELECT 
    t.AwbPrefix, t.AwbNo, t.AwbPostfix,
    STUFF((SELECT ',' + CAST(manifestId AS VARCHAR(MAX)) 
           FROM 
               (
                   SELECT d.Manifest_ID manifestId
                   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
                   WHERE d.AwbNo = t.AwbNo
                   UNION 
                   SELECT d.Manifest_ID manifestId
                   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
                   WHERE ha.AwbNo = t.AwbNo
               ) AS manifestIds
           FOR XML PATH('')), 1, 1, '') AS manifestIds,
    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 
    (
        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
        WHERE d.AwbNo = '55907865'
        UNION 
        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
        WHERE ha.AwbNo = '55907865'
    ) t;

2、在WHERE语句中指定需要合并的记录，例如WHERE d.AwbNo = '55907865'。

3、执行查询后，将会得到一条记录，其中manifestIds字段的值为逗号分隔的manifestId值。

如果以上回答对您有所帮助，点击一下采纳该答案～谢谢

引用chatGPT作答，可以使用STRING_AGG()函数将多行结果合并为一行，并使用GROUP BY将结果按照其他字段分组。

以下是如何修改您的查询来合并manifestId字段：

SELECT t.AwbPrefix, t.AwbNo, t.AwbPostfix, t.hwbNo, STRING_AGG(t.manifestId, ',') AS manifestIds, 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, ISNULL(t.white, NULL) AS white, t.HouseId
FROM (
    SELECT d.AwbPrefix, d.AwbNo, d.AwbPostfix, NULL AS hwbNo, d.Manifest_ID AS manifestId, b.Routing, b.AgentCode, b.Goods AS 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
    WHERE d.AwbNo = '55907865'
    UNION ALL
    SELECT d.AwbPrefix, d.AwbNo, d.AwbPostfix, ha.HwbNo AS hwbNo, d.Manifest_ID AS manifestId, b.Routing, b.AgentCode, b.Goods AS 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
    WHERE d.AwbNo = '55907865'
) t
GROUP BY t.AwbPrefix, t.AwbNo, t.AwbPostfix, t.hwbNo, 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
ORDER BY t.AwbNo, t.hwbNo ASC
OFFSET #{dto.pageNum} * #{dto.pageSize} ROWS FETCH NEXT #{dto.pageSize} ROWS ONLY;

可以使用STUFF函数将每个Manifest_ID的值合并成一条，具体实现可按如下方式修改：

SELECT t.*,
    STUFF((SELECT ',' + CONVERT(VARCHAR(10),d.Manifest_ID)
           FROM TBL_DepManifest d with(nolock)
           WHERE d.AwbNo = t.AwbNo AND d.AwbPrefix = t.AwbPrefix AND d.AwbPostfix = t.AwbPostfix
           FOR XML PATH('')), 1, 1, '') as manifestIds
FROM (
    SELECT d.AwbPrefix,d.AwbNo,d.AwbPostfix,NULL hwbNo,d.Manifest_ID,
        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
    WHERE d.AwbNo = '55907865'
    UNION ALL
    SELECT d.AwbPrefix,d.AwbNo,d.AwbPostfix,ha.HwbNo,d.Manifest_ID,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
    WHERE d.AwbNo = '55907865'
) t
ORDER BY t.AwbNo, t.hwbNo ASC
OFFSET #{dto.pageNum} * #{dto.pageSize} ROWS FETCH NEXT #{dto.pageSize} ROWS ONLY;

在上述代码中，STUFF函数将查询结果中每个AwbNo、AwbPrefix和AwbPostfix相等的Manifest_ID合并成一个字符串，以逗号分隔，并将结果作为新的一列添加到查询结果中。具体实现方法为将查询结果中每个合并的字符串合并为XML格式，然后在XML字符串前加上逗号，并用STUFF函数将第一个逗号替换为空字符串。

以下答案由GPT-3.5大模型与博主波罗歌共同编写：
要将查询结果中的manifestId字段合并成逗号分隔的值，可以使用STRING_AGG()函数。以下是修改后的SQL语句：

SELECT
    d.AwbPrefix,d.AwbNo ,d.AwbPostfix,NULL hwbNo,
    STRING_AGG(d.Manifest_ID,', ') WITHIN GROUP (ORDER BY d.Manifest_ID ASC) AS 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
    (
        SELECT
            d.AwbPrefix,d.AwbNo ,d.AwbPostfix,NULL hwbNo,d.Manifest_ID,
            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,
            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'
GROUP BY
    d.AwbPrefix,d.AwbNo ,d.AwbPostfix,b.Routing,b.AgentCode,b.Goods,
    h.HsCode,h.HsNameCN,h.HsNameEN,h.Piece,h.Weight,h.PackageName,h.DeclaredValueForCustoms,
    h.CurrencyName,h.IsAgree,h.DataSource,ISNULL(l.AgentCode, NULL),h.HouseId
ORDER BY
    d.AwbNo,hwbNo ASC
OFFSET #{dto.pageNum} * #{dto.pageSize} ROWS FETCH NEXT #{dto.pageSize} ROWS ONLY

其中，使用STRING_AGG()函数将manifestId字段合并为逗号分隔的值。在GROUP BY子句中，除manifestId外的所有字段都要列出，以确保每个合并的行都被正确分组。
如果我的回答解决了您的问题，请采纳！

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

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