將同組數據用分隔符合併--將含分隔符的字串分開功能的反轉

/*1.首先創造函數*/

CREATE function getchr_together(@cid int,@chr varchar(10))
returns varchar(8000)
as

begin
 declare @str varchar(8000)
 set @str=''
 select @str=@str+@chr+ass from (
  select ass from tt_pn where cid=@cid group by ass
 ) a
 
 if(len(@str)>1)
     set @str=right(@str,len(@str)-len(@chr))

 return(case when rtrim(@str)='' then '' else rtrim(@str) end)
end

/*2.創建表及實現*/

create table tt_pn([cid][numeric](18,0) NOT NULL, ass varchar(20))

insert into tt_pn select '1', '6155CSAMWWSL15R'     
insert into tt_pn select '1', '6155BAAMWWSL15R'    
insert into tt_pn select '1', '6155B1AMXXXX15R'     
insert into tt_pn select '2', '8112MA5XGMXXXXR'  
insert into tt_pn select '3', '6219PCIVFRXXXXR'     
insert into tt_pn select '3', '6219PAIVFRXXXAR'

select cid, tkey=dbo.getchr_together(cid,'-')
from tt_pn group by cid