Store Column Result in a Table Cell

if object_id('Yourtable', 'U') is not null drop table YourTable

create table YourTable (id int,name varchar(10),URL varchar(100))
insert into YourTable select 1,'Test1','http://Dummy1'
insert into YourTable select 2,'Test1','http://Dummy2'
insert into YourTable select 3,'Test2','http://Dummy1'


select
     YourTable.ID,
     YourTable.Name,
     YourTable.URL,
     AllNames.Assign
from
     YourTable
 join
 (
   select  
      distinct
      URL ,
      ( select '~' + name
        from YourTable YT1
        where YT1.URL = YT2.URL
        for xml path('')
      )
      +'~' as Assign
   from
      YourTable YT2
 )AllNames on YourTable.URL = AllNames.URL
order by ID

gives you

ID          Name       URL             Assign
----------- ---------- --------------- ---------------
1           Test1      http://Dummy1   ~Test1~Test2~
2           Test1      http://Dummy2   ~Test1~
3           Test2      http://Dummy1   ~Test1~Test2~

(3 row(s) affected)