sql多行合并成一行用逗号隔开，多表联合查询中子查询取名可重复

复

简单版的

SELECT a.CreateBy,Name =stuff((

select ','+Name FROM SG_Client WHERE CreateBy = a.CreateBy for xml path('')),1,1,'')

FROM SG_Client a group by a.CreateBy

//连表查询

SELECT a.ContractID, LocationName =stuff(

( select ',' + LocationName FROM(select L.Name as LocationName,b.ContractID from SG_ContractBunk B inner join MALL_Location L on B.LocationID = L.LocationID ) tb where tb.ContractID=a.ContractID for xml path('')) ,1,1,'')

FROM(select L.Name as LocationName,b.ContractID from SG_ContractBunk B inner join MALL_Location L on B.LocationID = L.LocationID) a group by a.ContractID

select C.ContractID,ContractNO,ContractNO2,ContractName,State,CL.Name as ClientName,B.Name as BrandName,l.LocationName from SG_Contract C WITH (NOLOCK) inner join SG_Client CL WITH (NOLOCK) on C.ClientID=CL.ClientID inner join SG_Brand B WITH (NOLOCK) on c.BrandID=B.BrandID inner join (SELECT a.ContractID, LocationName =stuff(

( select ',' + LocationName FROM(select L.Name as LocationName,b.ContractID from SG_ContractBunk B inner join MALL_Location L on B.LocationID = L.LocationID ) tb where tb.ContractID=a.ContractID for xml path('')) ,1,1,'')

FROM(select L.Name as LocationName,b.ContractID from SG_ContractBunk B inner join MALL_Location L on B.LocationID = L.LocationID) a group by a.ContractID) L ON C.ContractID=L.ContractID