title: How can I concatinate a subquery result field into the parent query?
body: <p>DB: Sql Server 2008.</p>
  
  <p>I have a really (fake) groovy query like this:-</p>
  
  <pre><code>SELECT CarId, NumberPlate
      (SELECT Owner
       FROM Owners b
       WHERE b.CarId = a.CarId) AS Owners
  FROM Cars a
  ORDER BY NumberPlate
  </code></pre>
  
  <p>And this is what I'm trying to get...</p>
  
  <pre><code>=&gt; 1    ABC123     John, Jill, Jane
  =&gt; 2    XYZ123     Fred
  =&gt; 3    SOHOT      Jon Skeet, ScottGu
  </code></pre>
  
  <p>So, i tried using </p>
  
  <p>AS <code>[Text()] ... FOR XML PATH('')</code> but that was inlcuding weird encoded characters (eg. carriage return). ... so i'm not 100% happy with that.</p>
  
  <p>I also tried to see if there's a COALESCE solution, but all my attempts failed.</p>
  
  <p>So - any suggestions?</p>