sql server - Join a column into a string -

i think need better understand xml path working. maybe can me simple example:

given

declare @p varchar(200) declare @phn table (phone varchar(25)) insert @phn values('204-721-1532') insert @phn values('306-922-2058') insert @phn values('204-728-3852') 

i want change rows in @phn.phone varchar, which, if python, like

set @p = ', '.join(@phn.phone) # alas 

now, this works point:

select distinct phone+', ' @phn xml path('') ==> 204-721-1532, 204-728-3852, 306-922-2058,  

there's trailing comma, no problem there. need result varchar variable, like

select distinct @p = phone+', ' @phn xml path('') 

but that's not allowed (msg 6819, level 16, state 3, line 45/the xml clause not allowed in assignment statement.)

eventually, going go scalar function, , want function return joined string.

i think need:

sql fiddle

declare @p varchar(200) declare @phn table (phone varchar(25)) insert @phn values('204-721-1532') insert @phn values('306-922-2058') insert @phn values('204-728-3852')  --using isnull() function eliminate tralling comma select @p = isnull(@p + ', ','') + phone @phn  select @p  results:  204-721-1532, 306-922-2058, 204-728-3852