一段SQL

將廠商負責的多個採購用戶代碼 使用 用戶名出來。

 如"123,345,456" 替換成"張三,李四,王五" 

select vendorCode,vendorShortName,linkemail,left(isNull(buyer,','),len(isNull(buyer,','))-1) as buyer
from
(select distinct vendorCode,VendorShortName,linkemail,(
select a.UserName+','
from
( select b.VendorCode,b.VendorShortName,d.UserName from
(select distinct a.vendorCode,a.VendorShortName,a.linkemail,
SUBSTRING(a.Buyer,number,CHARINDEX(',',a.Buyer+',',number)-number)
as buyer from By_VendorInfo as a,master..spt_values as b where type='p' and number<LEN(a.Buyer)
and SUBSTRING(','+a.Buyer,number,1)=',' and a.PlantNo='P112'
) b,Privilege_UserInfo d where b.buyer=d.UserId
) as a where a.VendorCode=v.VendorCode for xml path('')) as buyer
from By_VendorInfo v where PlantNo='P112') f