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

转载于:https://www.cnblogs.com/heyiping/p/9402833.html