title: Concatenate strings while filtering by another column in SQL Server view
body: <p>My table <code>SC</code> is of the following format:</p>
  
  <pre><code>Team        Owner
  ------------------
   A            1
   A            2
   B            3
   B            4
  </code></pre>
  
  <p>I would like my output in the <strong>view</strong> to be:</p>
  
  <pre><code>Team        Owners
  -------------------
   A            1,2
   B            3,4
  </code></pre>
  
  <p>I tried using FOR XML PATH as follows in the view's design:</p>
  
  <pre><code>STUFF((SELECT ', ' + temp.Owner 
         FROM (SELECT SC.Owner) temp 
         WHERE (SC.Owner = temp.Owner) 
         FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 2, '') AS Owners
  </code></pre>
  
  <p>But it gives me an error, since this is a view's design and not a standard transaction: </p>
  
  <blockquote>
    <p>Error in WHERE clause near '('.<br>
    Unable to parse query text.`</p>
  </blockquote>
  
  <p>Any suggestions on how to achieve what I would like?</p>