sql server - Function with XML path doesnt work -

i want write function concat field different row , group id.
when execute code in tsql work fine, when execute function returns wrong data, why?

create table yourtable ([id] int, [name] char(1), [value] int)  insert yourtable ([id],[name],[value]) values (1,'a',4) insert yourtable ([id],[name],[value]) values (1,'b',8) insert yourtable ([id],[name],[value]) values (2,'c',9) insert yourtable ([id],[name],[value]) values (3,'d',9) insert yourtable ([id],[name],[value]) values (3,'u',9)  select      [id],     stuff((         select ', ' + [name]          yourtable          (id = results.id)          xml path(''),type).value('(./text())[1]','varchar(max)'),1,2,'') namevalues yourtable results group id; 

create     function dbo.concat_string_group_by_id (@id int , @name varchar     (100) ) returns table return cte as(     select @id id ,@name name     (values(0)) a(m)) select      [id],     stuff((         select ', ' + [name]          cte          (id = results.id)          xml path(''),type).value('(./text())[1]','varchar(max)'),1,2,'') namevalues cte results group id 

select n.*  yourtable cross apply dbo.concat_string_group_by_id (id,name) n 

first query output:

id  namevalues 1   a, b 2   c 3   d, u 

second query output:

id  namevalues 1   1   b 2   c 3   d 3   u 

your function wont work ,because cross apply executed each row of outer query once , outputs result

in first query,you evaluating data @ once , logical order of execution goes below

query:

select      [id],     stuff((         select ', ' + [name]          yourtable          (id = results.id)          xml path(''),type).value('(./text())[1]','varchar(max)'),1,2,'') namevalues yourtable results group id; 

1.group id table
2.do concat of ids present using xml

in second query ,you using cross apply to pass 1 row @ time , have 1 row concat ,so output varies