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
查询结果