SQL 多条记录整合到一个字段 一个字段拆分多条记录

--将mtMeetLists中MeetID为‘’的所有记录的Recipient字段处理并累加到RecipientDisplayName

select distinct RecipientDisplayName=


stuff((select name  +' ' from eemployee e inner join mtMeetLists t on t.Recipient=e.Adaccount 


where MeetID='7B5EBDF6-CF01-480B-900D-05597322018A'


and Recipient=t.Recipient for xml path('')), 1, 0, '')  


from mtMeetLists

--将某条记录内的Attchment拆分获取多条记录并拼成A标签返回

Attchment原内容格式：|07_02_10-04-22项目分工.xlsx:201507/%E5%AF%B9%E8%B1%A1%E5%90%8D%E7%A7%B0.xlsx|07_02_10-04-22项目数据库说明文档(new).docx:201507/%E5%AF%B9%E8%B1%A1%E5%90%8D%E7%A7%B0.docx

CREATE Proc oSP_GetAttchmentDetail

(


@ID uniqueidentifier


)


As


Begin 


Declare @Str nvarchar(1000),


@Len smallint,


@position smallint,


@BeginPoint smallint,


@position2 smallint,


@InputStr nvarchar(max)





Declare @Resu table  


(


Location  NVarchar(4000) COLLATE SQL_Latin1_General_CP1_CI_AS,


Location2 NVarchar(4000) COLLATE SQL_Latin1_General_CP1_CI_AS


)






Select @BeginPoint=1,@Str=''





Select @InputStr=Right(Attchment,Len(Attchment)-1) from mtmeets Where ID=@ID





Select @Len=Len(@InputStr),@position=CHARINDEX(N'|',@InputStr,@BeginPoint),@position2=CHARINDEX(N':',@InputStr,@BeginPoint) 


--from mtmeets where ID=@ID





While @position>0


Begin


Select @Str=substring(@InputStr,@BeginPoint,@position2-@BeginPoint) --from mtmeets Where ID=@ID


Set @BeginPoint=@position+1









Insert Into @Resu(Location,Location2) 


Select @Str,(Select substring(@InputStr,@position2+1,@position-@position2-1)) --from mtmeets Where ID=@ID)





Select @position=CHARINDEX(N'|',@InputStr,@BeginPoint),@position2=CHARINDEX(N':',@InputStr,@BeginPoint) --from mtmeets where ID=@ID


End





Select @Str=substring(@InputStr,@BeginPoint,@position2-@BeginPoint) --from mtmeets where ID=@ID






Insert Into @Resu(Location,Location2) 


Select @Str,(Select substring(@InputStr,@position2+1,@Len-@position2)) --from mtmeets where ID=@ID)





Select N'<a href="https://eip.sailunjinyu.com/mysite/_layouts/15/WopiFrame.aspx?sourcedoc=https://eip.sailunjinyu.com/mysite/FileLibrary/'


+Location2+N'">'+Right(Location,len(Location)-14)+N'</a>'


 


from @Resu


End