T-SQL如何实现多行合并为单行，差异列输出逗号分隔值

问题根因 #

拼接结果过长是因为CONTRACT表和ORDER表关联时，单个客户存在多条符合ORDERSTATEID=6的订单，导致同一条合同、同一种合同类型的记录被重复关联，最终STRING_AGG拼接了大量重复的合同类型值。

解决方案 #

方案1（适配SQL Server 2017及以上版本，修改成本最低）

直接在STRING_AGG中加入DISTINCT参数，自动去重后再拼接：

SELECT
   C.CONTRACTID,C.NUMBER, C.STATE , 
   O.CUSTOMERCODE,O.CUSTOMERNAME ,
   C.STARTDATE , C.TERMINATIONDATE ,
   STRING_AGG(DISTINCT CAST(CT.Name AS NVARCHAR(MAX)) , ',') AS CONTRACTTYPELIST
FROM 
  [DBO].[CONTRACT] C  
INNER JOIN 
  [ORDERING].[ORDER] O ON C.CUSTOMERID = O.CUSTOMERID 
INNER JOIN
   [dbo].[Contract_ContractType] CCT on CCT.ContractId =  C.ContractId
INNER  JOIN 
   [Ref].[ContractType] CT on CT.ContractTypeId = CCT.ContractTypeId
WHERE 
   O.ORDERSTATEID =  6 
GROUP BY  
C.CONTRACTID,C.NUMBER, C.STATE  , 
O.CUSTOMERCODE,O.CUSTOMERNAME ,
C.STARTDATE , C.TERMINATIONDATE

方案2（兼容低版本数据库，从根源消除重复关联）

先对订单表做去重处理，保证每个客户ID仅返回一行记录后再关联合同表：

WITH DistinctCustomerOrder AS (
    -- 提前过滤并去重符合条件的订单客户信息
    SELECT DISTINCT CUSTOMERID, CUSTOMERCODE, CUSTOMERNAME
    FROM [ORDERING].[ORDER]
    WHERE ORDERSTATEID = 6
)
SELECT
   C.CONTRACTID,C.NUMBER, C.STATE , 
   O.CUSTOMERCODE,O.CUSTOMERNAME ,
   C.STARTDATE , C.TERMINATIONDATE ,
   STRING_AGG(CAST(CT.Name AS NVARCHAR(MAX)) , ',') AS CONTRACTTYPELIST
FROM 
  [DBO].[CONTRACT] C  
INNER JOIN 
  DistinctCustomerOrder O ON C.CUSTOMERID = O.CUSTOMERID 
INNER JOIN
   [dbo].[Contract_ContractType] CCT on CCT.ContractId =  C.ContractId
INNER  JOIN 
   [Ref].[ContractType] CT on CT.ContractTypeId = CCT.ContractTypeId
GROUP BY  
C.CONTRACTID,C.NUMBER, C.STATE  , 
O.CUSTOMERCODE,O.CUSTOMERNAME ,
C.STARTDATE , C.TERMINATIONDATE

内容的提问来源于stack exchange，提问作者Raq