title: Query to concatenate rows into string works in Select Statement, but not in Table Valued Function. What am I missing?
body: <p>I have a query that will concatenate multiple rows into a delimited list. I am trying to create a table-valued function to make this more repeatable and easier to use, but it doesn't work when using this in a function.</p>
  
  <p><strong>Here is some test data I am using to build this out:</strong></p>
  
  <pre><code>create table #test (id int not null, val varchar(max))
  insert into #test values(1, 'a')
  insert into #test values(1, 'b')
  insert into #test values(1, 'c')
  insert into #test values(2, 'd')
  insert into #test values(2, 'e')
  insert into #test values(2, 'f')
  insert into #test values(3, 'g')
  insert into #test values(3, 'h')
  insert into #test values(3, 'i')
  </code></pre>
  
  <p><strong>Code to concatenate rows into list by ID group:</strong></p>
  
  <pre><code>SELECT  distinct
  id
  --,@values as values
  ,  STUFF((
      SELECT ', ' + val
      FROM 
          (select t.id, val from #test t ) A
       WHERE a.id = b.id
      FOR XML PATH(''),TYPE 
       /* Use .value to uncomment XML entities e.g. &amp;gt; &amp;lt; etc*/
      ).value('.','NVARCHAR(MAX)') 
    ,1,2,'') as list
  FROM    
  (select t.id, val from #test t)  B
  GROUP BY id, val
  </code></pre>
  
  <p><strong>When I run the following code by itself, here is the before and after result as expected:</strong></p>
  
  <pre><code>Before:                 After:
  
  id  val             id   list
  1    a              1    a, b, c
  1    b              2    d, e, f
  1    c              3    g, h, i
  2    d  
  2    e  
  2    f  
  3    g  
  3    h  
  3    i  
  </code></pre>
  
  <p>I have tried several ways to create a TVF with this same code, however I am not getting the expected results. </p>
  
  <p><strong>Code used to create function:</strong></p>
  
  <pre><code>CREATE FUNCTION [dbo].[LIST_AGG] 
  (   
      @id int,
      @val nvarchar(max) 
  )
  RETURNS TABLE 
  AS
  RETURN 
  (
  SELECT  
  id
  ,val
  ,STUFF((
      SELECT ', ' + val
      FROM 
          (select @id id , @val val) A
       WHERE a.id = b.id
      FOR XML PATH(''),TYPE 
       /* Use .value to uncomment XML entities e.g. &amp;gt; &amp;lt; etc*/
      ).value('.','NVARCHAR(255)') ,1,2,'') as list
  FROM    
  (select @id id , @val val) B
  
  GROUP BY id, val
  )
  </code></pre>
  
  <p><strong>Code used to call function</strong></p>
  
  <pre><code>select 
  t.id
  , t.val
  , S.list 
  from #test t
  cross apply dbo.list_AGG(t.id,t.val) as s
  </code></pre>
  
  <p><strong>And here is the unexpected result when calling the function:</strong></p>
  
  <pre><code>id  val list
  1   a   a
  1   b   b
  1   c   c
  2   d   d
  2   e   e
  2   f   f
  3   g   g
  3   h   h
  3   i   i
  </code></pre>
  
  <p>The 'list' column should be concatenated. I have tried several different ways of accomplishing this to no avail. </p>