How can I concatinate a subquery result field into the parent query?

Question

Hi folks,

DB: Sql Server 2008.

I have a really (fake) groovy query like this:-

SELECT CarId, NumberPlate
    (SELECT Owner
     FROM Owners b
     WHERE b.CarId = a.CarId) AS Owners
FROM Cars a
ORDER BY NumberPlate

And this is what I'm trying to get...

=> 1    ABC123     John, Jill, Jane
=> 2    XYZ123     Fred
=> 3    SOHOT      Jon Skeet, ScottGu

So, i tried using

AS [Text()] ... FOR XML PATH('') but that was inlcuding weird encoded characters (eg. carriage return). ... so i'm not 100% happy with that.

I also tried to see if there's a COALESCE solution, but all my attempts failed.

So - any suggestions?

Answer 1

Try the solution to this question:

http://stackoverflow.com/questions/6899/is-there-a-way-to-create-a-mssql-function-to-join-multiple-rows-from-a-subquery

:)