title: Row concatenation with FOR XML, but with multiple columns?
body: <p>I often use queries like:</p>
  
  <pre><code>SELECT *
  FROM   ThisTable
         OUTER APPLY (SELECT (SELECT SomeField + ' ' AS [data()]
                              FROM   SomeTable
                              WHERE  SomeTable.ID = ThisTable.ID
                              FOR XML PATH ('')) AS ConcatenatedSomeField) A 
  </code></pre>
  
  <p>I often want to get multiple concatenated concatenated fields from this table, instead of just one. I could logically do this:</p>
  
  <pre><code>SELECT *
  FROM   ThisTable
         OUTER APPLY (SELECT (SELECT SomeField + ' ' AS [data()]
                              FROM   SomeTable
                              WHERE  SomeTable.ID = ThisTable.ID
                              FOR XML PATH ('')) AS ConcatenatedSomeField) A
         OUTER APPLY (SELECT (SELECT SomeField2 + ' ' AS [data()]
                              FROM   SomeTable
                              WHERE  SomeTable.ID = ThisTable.ID
                              FOR XML PATH ('')) AS ConcatenatedSomeField2) B
         OUTER APPLY (SELECT (SELECT SomeField3 + ' ' AS [data()]
                              FROM   SomeTable
                              WHERE  SomeTable.ID = ThisTable.ID
                              FOR XML PATH ('')) AS ConcatenatedSomeField3) C 
  </code></pre>
  
  <p>But it looks crappy and error prone when anything needs to be updated; also SomeTable is often a long list of joined tables so it could also have performance implications getting the same tables over and over. </p>
  
  <p>Is there a better way to do this? </p>
  
  <p>Thanks.</p>