相同单号合成一行

本想用游标来解决的(可是你懂得的，游标性能不好，逻辑还复杂，对于这种问题，可能还会用到双游标!)

在网上看了看还是用for xml path

先贴图片:

要变成这如下图片这样子

这是代码如下代实现
 WITH freeroom
  AS ( select OM.RefNo, OM.PONo, OM.OrderDate, AU.[CompanyName], AU.[FTY], OM.[DesignNo],
   OC.[CareCode], MC.[English],
  Case 
   when  OT.[Type]  = '' then   OT.[Percentage] + '% ' + MCT.[English] 
    else  MT.[English] + ' ' +  OT.[Percentage] + '% ' + MCT.[English]
    end as 'content',  
   OOT.QTY,OM.OrderType
  From [O_OrderMaster] OM

  Inner Join [O_OrderCareCode] OC ON OC.RefNo = OM.RefNo
  Inner Join [M_CareCodeMulti] MC ON MC.[CareCode] = OC.[CareCode]
  Inner Join [A_User] AU ON AU.[CustID] = OM.[CustID]
  Inner Join [O_OrderContent] OT ON OT.RefNo = OC.RefNo
  Inner Join [M_Content] MCT ON MCT.[ContCode] = OT.[ContentCode]
  Inner Join [O_OrderOtherInfo] OOT ON OOT.RefNo = OC.RefNo
  Left Join [M_Type] MT ON MT.[Type] = OT.[Type]
  Where MC.[English] <> '' and OM.State <> '3' ) --OrderType
   
    -- select * from freeroom order by RefNo
 
  -----For XML Path
    SELECT B.RefNo,B.PONo,B.OrderDate,B.CompanyName,B.FTY,
     B.DesignNo,B.CareCode,B.English,B.QTY,B.OrderType,ConTent FROM (
      SELECT RefNo,PONo,OrderDate,CompanyName,FTY,DesignNo,
               CareCode,English,QTY,OrderType,
    (SELECT content+',' FROM freeroom WHERE RefNo=A.RefNo FOR XML PATH(''))AS ConTent
      FROM freeroom A
    GROUP BY RefNo,PONo,OrderDate,CompanyName,FTY,DesignNo,
 CareCode,English,QTY,OrderType
) B