update tablename set col2=col2+isnull(b.col2,'') from tablename a left join (select * from tablename where right(col1,1)='F')b on left(a.col1,3)=left(b.col1,3) where right(a.col1,1)<>'F'
create table #tb (number nvarchar(10),string nvarchar(4000)) insert #tb select 'A410','A410_Str' union all select 'A411','A411_Str' union all select 'A412','A412_Str' union all select 'A413','A413_Str' union all select 'A414','A414_Str' union all select 'A41F','E3N4' union allselect 'A420','A420_Str' union all select 'A421','A421_Str' union all select 'A422','A422_Str' union all select 'A423','A423_Str' union all select 'A424','A424_Str' union all select 'A42F','E3N4' union allselect 'A510','A510_Str' union all select 'A511','A511_Str' union all select 'A512','A512_Str' union all select 'A513','A513_Str' union all select 'A514','A514_Str' union all select 'A51F','E3N4' update #tb set string=stuff((select ','+string from #tb as t2 where Left(#tb.number,3)=Left(t2.number,3) and right(number,1)<>'F' for xml path('')),1,1,'') where right(number,1)='F'select * from #tb where right(number,1)='F'--number string ------------ ------------------------------------------------ --A41F A410_Str,A411_Str,A412_Str,A413_Str,A414_Str --A42F A420_Str,A421_Str,A422_Str,A423_Str,A424_Str --A51F A510_Str,A511_Str,A512_Str,A513_Str,A514_Str
update tablename set col2=col2+isnull(b.col2,'')
from tablename a left join
(select * from tablename where right(col1,1)='F')b
on left(a.col1,3)=left(b.col1,3)
where right(a.col1,1)<>'F'
create table #tb
(number nvarchar(10),string nvarchar(4000))
insert #tb
select 'A410','A410_Str' union all
select 'A411','A411_Str' union all
select 'A412','A412_Str' union all
select 'A413','A413_Str' union all
select 'A414','A414_Str' union all
select 'A41F','E3N4' union allselect 'A420','A420_Str' union all
select 'A421','A421_Str' union all
select 'A422','A422_Str' union all
select 'A423','A423_Str' union all
select 'A424','A424_Str' union all
select 'A42F','E3N4' union allselect 'A510','A510_Str' union all
select 'A511','A511_Str' union all
select 'A512','A512_Str' union all
select 'A513','A513_Str' union all
select 'A514','A514_Str' union all
select 'A51F','E3N4' update #tb set string=stuff((select ','+string from #tb as t2 where Left(#tb.number,3)=Left(t2.number,3)
and right(number,1)<>'F' for xml path('')),1,1,'')
where right(number,1)='F'select * from #tb where right(number,1)='F'--number string
------------ ------------------------------------------------
--A41F A410_Str,A411_Str,A412_Str,A413_Str,A414_Str
--A42F A420_Str,A421_Str,A422_Str,A423_Str,A424_Str
--A51F A510_Str,A511_Str,A512_Str,A513_Str,A514_Str