Function with XML path doesnt work

Question

I want to write a function that concat field from different row and group by id.
When I execute my code in TSql is work fine, But when I execute it as a function it returns wrong data, Why?

CREATE TABLE YourTable ([ID] INT, [Name] CHAR(1), [Value] INT)

INSERT INTO YourTable ([ID],[Name],[Value]) VALUES (1,'A',4)
INSERT INTO YourTable ([ID],[Name],[Value]) VALUES (1,'B',8)
INSERT INTO YourTable ([ID],[Name],[Value]) VALUES (2,'C',9)
INSERT INTO YourTable ([ID],[Name],[Value]) VALUES (3,'d',9)
INSERT INTO YourTable ([ID],[Name],[Value]) VALUES (3,'u',9)

SELECT 
    [ID],
    STUFF((
        SELECT ', ' + [Name] 
        FROM YourTable 
        WHERE (ID = Results.ID) 
        FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)'),1,2,'') AS NameValues
FROM YourTable Results
GROUP BY id;

create     FUNCTION dbo.CONCAT_String_group_by_id (@id int , @name varchar    
(100) )
RETURNS TABLE AS return
WITH cte AS(
    SELECT @id id ,@name name
    FROM (VALUES(0)) a(m))
SELECT 
    [ID],
    STUFF((
        SELECT ', ' + [Name] 
        FROM cte 
        WHERE (ID = Results.ID) 
        FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)'),1,2,'') AS NameValues
FROM cte Results
GROUP BY id

SELECT n.* 
FROM 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   A
1   B
2   C
3   d
3   u

Answer 1

Your function wont work ,because Cross apply will be executed for each row of outer query once and outputs the result

In your first query,you are evaluating all the data at once and logical order of execution goes like below

query:

SELECT 
    [ID],
    STUFF((
        SELECT ', ' + [Name] 
        FROM YourTable 
        WHERE (ID = Results.ID) 
        FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)'),1,2,'') AS NameValues
FROM YourTable Results
GROUP BY id;

1.group by id from your table
2.Do a concat of all IDs Present using XML

In your second query ,you are using cross apply to pass one row at a time and it will have only one row to concat ,so your output varies