title: SQL Server concatenation of records with aggregate function in select statement
body: <p>I have a view vw_XC_DocInfo_1 with columns VId,VName,DocId,Amount,INum. Below is the table data.</p>
  
  <pre><code>Vid   VName  DocId  Amount  INum
  1     ABC     10     100    INV1
  1     ABC     11     10     INV2
  1     ABC     12     20     INV3
  1     ABC     13     30     INV4
  2     XYZ     14    200     INV5
  2     XYZ     15     10     INV6
  2     XYZ     16     20     INV7
  2     XYZ     17     30     INV8
  </code></pre>
  
  <p>I need to display output like below.</p>
  
  <pre><code>Vid   VName  DocIdsList  Amount  INumList
  1     ABC    10,11       110     INV1,INV2
  1     ABC    12,13       50      INV3,INV4
  2     XYZ    14,15       210     INV5,INV6
  3     XYZ    16,17       50      INV7,INV8
  </code></pre>
  
  <p>I have tried different ways but unable to include aggregate function with STUFF function, please find the query I have tired.</p>
  
  <pre><code>with CTE
  as (
      select top 20 V.VendorId,
          V.VendorName,
          STUFF((
                  select top 3 ',' + CONVERT(varchar(MAX), V1.DocumentId)
                  from vw_XC_DocInfo_1 V1
                  where V1.VendorID = V.VendorId
                  order by V1.DocumentId
                  for xml PATH('')
                  ), 1, 1, '') as DocIdsList,
          STUFF((
                  select top 3 ',' + CONVERT(varchar(MAX), V1.InvoiceNumber)
                  from vw_XC_DocInfo_1 V1
                  where V1.VendorID = V.VendorId
                  order by V1.InvoiceNumber
                  for xml PATH('')
                  ), 1, 1, '') as InvNumList
      from vw_XC_DocInfo_1 V
      order by V.VendorID
      )
  select VendorId,
      VendorName,
      DocIdsList,
      InvNumList
  from CTE
  group by VendorId,
      VendorName,
      DocIdsList,
      InvNumList
  </code></pre>